Previous articles in this series have:
- Introduced the Apache Derby database.
- Introduced the
- Demonstrated how to create a database schema, design relational database tables, and insert data into tables.
- Demonstrated how to extract data by writing SQL queries.
One important task that has not yet been covered is how to modify existing data. This article introduces the SQL
UPDATE statements, which you can use to selectively delete or modify existing data in an Apache Derby database.
To follow along with the examples in this article, you need to:
- Have a working Apache Derby database installation, which was covered in the first article in this series.
- Be familiar with the Apache Derby
ijcommand-line tool, which was covered in the second article in this series.
- Have a properly initialized Bigdog's Surf Shop example database, which was detailed in the fourth and fifth articles in this series.
- Be familiar with the basics of the SQL
SELECTstatement, which was discussed in the fifth article in this series.
If you haven't done so, make sure you've completed the steps listed above before proceeding with the rest of this article, which you can easily do by reviewing the previous articles in this series.
The first data-modification technique this article will address is deleting data. To delete
data in an Apache Derby database, you use the SQL
statement, which can delete either all rows in a table or a specific subset of rows.
The formal syntax for the SQL
DELETE statement you can use with the Apache Derby database is
remarkably simple, as you can see below:
DELETE FROM tableName
DELETE statement deletes all rows from the specified table that satisfy an optional
WHERE clause. If no
WHERE clause is included, all rows in the table are deleted. To demonstrate
this use of the
DELETE statement, create a temporary table, insert several rows, and delete
them all, as shown in Listing 1.
Listing 1. Deleting rows
ij> CREATE TABLE bigdog.temp (aValue INT) ; 0 rows inserted/updated/deleted ij> INSERT INTO bigdog.temp VALUES(0), (1), (2), (3) ; 4 rows inserted/updated/deleted ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ; COUNT ----------- 4 1 row selected ij> DELETE FROM bigdog.temp ; 4 rows inserted/updated/deleted ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ; COUNT ----------- 0 1 row selected ij> DROP TABLE bigdog.temp ; 0 rows inserted/updated/deleted
This example creates a single-column temporary table that holds an integer value. You insert
four rows into the database and issue a
SELECT statement to verify that the new table contains
four rows. By using an unconstrained
DELETE statement, you delete all four rows from the temporary
table, which is verified by both the message from Apache Derby --
inserted/updated/deleted -- and the second
SELECT statement, which indicates that the temporary table contains zero rows. Finally, the
DROP TABLE statement deletes the empty table from the schema.
In general, however, you don't want to delete all rows from a table; instead, you'll selectively delete rows. To do this, you create an appropriate
WHERE clause that identifies all rows of interest. The syntax for
WHERE clause that you can use with a
statement is identical to that discussed in Part 4, which presents the full SQL
SELECT statement syntax.
The basic building blocks for constructing a Boolean expression within a
WHERE clause were presented in Table 1 of that article and are
demonstrated here in Listing 2, where you delete all rows that satisfy at least one of two conditions.
Listing 2. Deleting selected rows
ij> DELETE FROM bigdog.products WHERE description LIKE '%towel%' OR itemNumber <= 3 ; 5 rows inserted/updated/deleted ij> SELECT itemNumber, description FROM bigdog.products ; ITEMNUMBER |DESCRIPTION ---------------------------------------------------- 4 |Male bathing suit, blue 5 |Female bathing suit, one piece, aqua 6 |Child sand toy set 9 |Flip-flop 10 |Open-toed sandal 5 rows selected
In this example, the
DELETE statement includes a
WHERE clause that identifies five rows, which you can see from the helpful
5 rows inserted/updated/deleted returned by Apache Derby via the
ij tool. The
WHERE clause contains two
expressions that are joined by the
OR operator, which means that if
either expression evaluates as
TRUE for a specific row, that row will be deleted.
The first expression finds all rows that contain the word "towel" in the product
description. If you recall from the previous articles in this series (or else issue a
SELECT statement prior to the
DELETE statement), there are two towels in the
bigdog.products table, with
values of 7 and 8. The other expression selects all rows with an
itemNumber column value less than or equal to 3. The contents of the
bigdog.products table are finally displayed with a simple
SELECT statement, demonstrating that only five of the original 10 rows
remain in the table.
Although this example doesn't explicitly demonstrate their use, you can also
include the SQL functions discussed in
Part 5 to
gain more control over the selection of rows for deletion. These same functions and other
operators that can be used in the
WHERE clause of the
DELETE statement also can be used with the
UPDATE statement to selectively modify the values of rows in a table, as
described in the next section.
The last SQL task for dealing with data that you need to address is updating specific column
values for selected rows in a table. At some level, the SQL
statement is the union of the SQL
DELETE statements, because you must select rows to modify as well as specify how to
modify them. Formally, the
UPDATE statement syntax is
straightforward, because you must specify the new column values for the set of rows to be updated, as
shown in Listing 3.
Listing 3. SQL UPDATE statement syntax
UPDATE tableName SET columnName = Value [ , columnName = Value} ]* [WHERE clause]
As shown in this SQL syntax, an SQL
statement must have, at a minimum, one
SET component to update one
column, along with one or more
SET components and a
WHERE clause, both of which are optional. If the
WHERE clause isn't included, the
UPDATE statement modifies the indicated columns for all rows in the
UPDATE statement is fairly easy, as shown in
Listing 4, where you modify two columns of a single row.
Listing 4. Updating selected rows
ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ; ITEMNUMBER |PRICE |STOCKDATE ------------------------------- 6 |9.95 |2006-01-15 1 row selected ij> UPDATE bigdog.products SET price = price * 1.25, stockDate = CURRENT_DATE WHERE itemNumber = 6 ; 1 row inserted/updated/deleted ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ; ITEMNUMBER |PRICE |STOCKDATE ------------------------------- 6 |12.43 |2006-06-20 1 row selected
This example wraps a single
UPDATE statement with
SELECT statements to demonstrate the change to the target row. The
SELECT statements both select three columns from the
bigdog.products table for a single row (the row with the value 6 in
itemNumber column). The
modifies both the
price and the
columns for this specific row. The value in the
price column is
increased by 25% (for example, perhaps due to the item's popularity), and the
stockDate column is modified to hold the current date, which can be obtained easily in Apache Derby by using the
CURRENT_DATE built-in function in an SQL
Apache Derby includes several of these built-in functions, which you can use to obtain data relevant to a current database connection. The full list of these built-in functions appears in Table 1.
Table 1. The Apache Derby SQL current functions
|Returns the current date in a
suitable Apache Derby |
|Returns the current transaction isolation level, which will be discussed in more detail in a future article, as a two-character string|
|Returns the schema name as a string of up to 128 characters that is used to qualify unqualified database object names|
|Returns the current time in a
suitable Apache Derby |
|Returns the current timestamp in a
suitable Apache Derby |
|Returns the authorized identifier as a string of up to 128 characters of the current user,
The previous example demonstrated how to modify multiple column values for a specific row in a
single table. However, sometimes the logic to select rows to update is more complex. For example,
suppose you need to modify the price of all objects in the
bigdog.products table that you obtain from Quiet Beach Industries, which
has a value of 3 in the
vendorNumber column in the
table. To do this, you need to use an embedded query, as shown in Listing 5.
Listing 5. Updating rows by using an embedded SELECT
ij> UPDATE bigdog.products SET price = price * 1.10, description = 'NEW: ' || description WHERE itemNumber IN ( SELECT v.itemNumber FROM bigdog.products as p, bigdog.vendors as v WHERE p.itemNumber = v.itemNumber AND v.vendorNumber = 3 ) ; 2 rows inserted/updated/deleted ij> SELECT * FROM bigdog.products ; ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 4 |29.95 |2006-02-10|Male bathing suit, blue 5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua 6 |12.43 |2006-06-20|Child sand toy set 9 |14.24 |2006-03-12|NEW: Flip-flop 10 |38.44 |2006-01-24|NEW: Open-toed sandal 5 rows selected
In this example, the
UPDATE statement modifies the
description columns for all products
that are obtained from the vendor with a value of 3 in the
vendorNumber column in the
table. Because you can't do a simple join within an
UPDATE statement, you
must include a subquery in the
WHERE clause to extract the
itemNumber rows that correspond to products from Quiet Beach
WHERE clause in the
statement uses the
IN operator to select those rows that have an
itemNumber column in the set of values selected by the embedded query.
Two types of queries can be used in the
WHERE clause of
UPDATE statement: a scalar subquery and a table
subquery. A scalar subquery is an embedded query that returns a single row that contains a
single column -- essentially, a single value, which is known as a scalar. You can use a scalar subquery to select a specific value that will be used in the expression of the
WHERE clause. For example,
itemNumber = (Scalar Subquery) updates any rows that have a value
itemNumber column that matches the result of the scalar subquery.
A table subquery, on the other hand, can return multiple rows that generally only have one column. In certain instances, a
table subquery can contain multiple columns. To use a table subquery, you need to use an SQL operator to combine the embedded query with a
Boolean expression. For example, this was shown in the previous code listing, where the
IN operator selected all rows from the
bigdog.products table that were produced by Quiet Beach
IN operator is one of four SQL operators that you can
use with a table subquery. All four of these operators are discussed in Table 2.
Table 2. The Apache Derby SQL operators and table subqueries
|Called a quantified comparison, because the |
|Another quantified comparison, but in this case the result is |
By using the information in Table 2, you should see that if you rewrite the
WHERE clause in the
UPDATE statement shown in
Listing 4 to use a quantified comparison and the same table subquery,
itemNumber = ANY (...), you get the same result. If you use the
ALL operator and the same table subquery, no rows are updated because all
itemNumber values in the
bigdog.products table aren't in the table subquery. On the other hand, if
you use the
EXISTS operator, all rows are modified because at least one of the
itemNumber values exists in the table subquery.
Modify the table schema
The previous section discussed modifying the data that already exists in a table. The other possibility is modifying the structure, or schema, of a database table. This can take the form of adding a column, changing the data type for a column, adding a constraint, or even deleting a column. This process isn't easy, which is one reason to be careful when you initially design your schema. If you do need to modify the structure of a table, you need to use a temporary table, as shown in Listing 6.
Listing 6. Updating a table
ij> CREATE TABLE bigdog.newProducts ( itemNumber INT NOT NULL, price DECIMAL(5, 2), stockDate DATE, count INT NOT NULL DEFAULT 0, description VARCHAR(40) ) ; 0 rows inserted/updated/deleted ij> INSERT INTO bigdog.newProducts(itemNumber, price, stockDate, description) SELECT itemNumber, price, stockDate, description FROM bigdog.products ; 5 rows inserted/updated/deleted ij> DROP TABLE bigdog.products ; 0 rows inserted/updated/deleted ij> RENAME TABLE bigdog.newProducts TO products ; 0 rows inserted/updated/deleted ij> SELECT * FROM bigdog.products ; ITEMNUMBER |PRICE |STOCKDATE |COUNT |DESCRIPTION ------------------------------------------------------------------------------------ 4 |29.95 |2006-02-10|0 |Male bathing suit, blue 5 |49.95 |2006-02-20|0 |Female bathing suit, one piece, aqua 6 |12.43 |2006-06-20|0 |Child sand toy set 9 |14.24 |2006-03-12|0 |NEW: Flip-flop 10 |38.44 |2006-01-24|0 |NEW: Open-toed sandal 5 rows selected
As this example shows, to modify a table -- in this case, to add a new
bigdog.products table -- you first create a table that has the
exact schema you require. This example requires that it always have a valid value by including
the column constraint
NOT NULL and assigns a default value of 0 to the
count column by using the column constraint
0. Notice how you can combine multiple column constraints by listing them
The next step is to copy the
existing data from the original table
to the new table. You can do so by using an SQL
that uses a subquery to get the values to insert. This is a powerful technique that lets you
easily copy all or part of an existing table into a second table.
After you've created the new table and copied the appropriate data, you drop the old table by
using an SQL
DROP TABLE statement and rename the new table to the
original name by using an SQL
RENAME TABLE statement. The rename
operation is straightforward: Rename the oldTableName to the newTableName, but don't supply a schema name for the new table name because the
RENAME operation can't move a table between different database schemas.
This example concludes by issuing a
SELECT statement to display the
schema and contents of the new
bigdog.products table. As you can see,
the new table has five columns, and the
count column is always zero. At
this point, a real application would modify the
appropriately by issuing the necessary SQL
This article focused exclusively on modifying data in an Apache Derby database. The first data
modification technique discussed was data deletion, which is performed by using the SQL
DELETE statement. Next you used the SQL
statement to modify the column values for selected rows in a table. Finally, you used a temporary
table to modify the structure of an existing database table. The article also demonstrated how
to modify a more complex database schema by using embedded subqueries. The next article will cover
a few remaining advanced database topics, after which this series will begin discussing how to connect to
an Apache Derby database from a Java application.
- Check out the other articles in this series:
- The first article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Introduction to Apache Derby" (developerWorks, February 2006), introduces the Apache Derby database and provides the foundation for many topics in this series.
- The second article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 1" (developerWorks, March 2006), introduces the
ijtool and demonstrates how to use it to connect to an Apache Derby database.
- The third article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part
2" (developerWorks, April 2006), introduces the concepts of a database schema and relational database tables, and demonstrates how to create basic
database objects by using the SQL
- The fourth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part
3" (developerWorks, May 2006), introduces the concept of executing SQL scripts with Apache Derby and demonstrates how to
insert data into tables in an Apache Derby database by using the SQL
- The fifth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part
4" (developerWorks, June 2006), introduces the concept of an SQL query and demonstrates how to extract data from an Apache
Derby database by using the SQL
- The sixth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 5" (developerWorks, July 2006), builds on the fifth article in this series to demonstrate how to write more powerful SQL queries, including those that make use of the SQL functions supported by the Apache Derby database.
- Use Apache Derby Project online manuals to get more detailed information on how to use the Apache Derby database.
- Learn how to download and install Apache Derby.
- Visit the developerWorks Open source zone for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.
- Check out the developerWorks Apache Derby project area for articles, tutorials, and other resources to help you get started with Derby today.
- Visit the Apache Derby Project Web site.
- Browse all the Apache articles and free Apache tutorials available in the developerWorks Open source zone.
- Browse for books on these and other technical topics at the Safari bookstore.
Get products and technologies
- Download the latest release of Apache Derby.
- Innovate your next open source development project with IBM trial software, available for download or on DVD.
- Get involved in the developerWorks community by participating in developerWorks blogs.