![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Previous articles in this series have:
- Introduced the Apache Derby database.
- Introduced the
ijtool. - 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 DELETE and 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 DELETE
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
[WHERE clause]
The 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 -- 4 rows
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
the WHERE clause that you can use with a DELETE
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
message 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 itemNumber column
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 UPDATE
statement is the union of the SQL INSERT and 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 UPDATE
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
table.
Issuing an 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
the itemNumber column). The UPDATE statement
modifies both the price and the stockDate
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
query.
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
| Function | Description |
|---|---|
CURRENT_DATE | Returns the current date in a
suitable Apache Derby DATE format |
CURRENT_ISOLATION | Returns the current transaction isolation level, which will be discussed in more detail in a future article, as a two-character string |
CURRENT_SCHEMA | Returns the schema name as a string of up to 128 characters that is used to qualify unqualified database object names |
CURRENT_TIME | Returns the current time in a
suitable Apache Derby TIME format |
CURRENT_TIMESTAMP | Returns the current timestamp in a
suitable Apache Derby TIMESTAMP format |
CURRENT_USER | Returns the authorized identifier as a string of up to 128 characters of the current user,
or APP if there is no 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 bigdog.vendors
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 price and description columns for all products
that are obtained from the vendor with a value of 3 in the vendorNumber column in the bigdog.vendors
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
Industries. The WHERE clause in the UPDATE
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
an 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
in the 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
Industries. The 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
| Operator | Example | Description |
|---|---|---|
IN | itemNumber IN (Table Subquery) | Returns TRUE if the value of the expression is in the table
subquery, which can only return a single column. You can include a NOT operator, as in NOT
IN, to select only those rows not in the table subquery. |
EXISTS | EXISTS (Table Subquery) | Returns TRUE if the table subquery returns any rows or FALSE if no rows are selected. This
means either all rows or none are modified, depending on the number of rows selected by
the table subquery. You can include a NOT operator to invert this
rule. |
ALL | itemNumber = ALL (Table Subquery) | Called a quantified comparison, because the ALL keyword modifies
a comparison operator (one of =, <,
>, <=, >=, or <>) so that the result is TRUE
only if it is true for all of the rows. The table subquery can return multiple rows, but they must
have only one column. |
ANY | itemNumber = ANY (Table Subquery) | Another quantified comparison, but in this case the result is TRUE if it is true
for any of the rows. SOME can be used as a synonym for ANY. The table subquery can return multiple rows, but they must
have only one column. |
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, WHERE
itemNumber = ANY (...), you get the same result. If you use the ALL operator and the same table subquery, no rows are updated because all
the 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.
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 count column
to the 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 DEFAULT
0. Notice how you can combine multiple column constraints by listing them
sequentially.
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 INSERT statement
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 count column
appropriately by issuing the necessary SQL UPDATE statements.
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 UPDATE
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.
Learn
- 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
CREATEstatement. - 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
INSERTstatement. - 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
SELECTstatement. - 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.
Discuss
- Get involved in the developerWorks community by participating in developerWorks blogs.
Robert J. Brunner is a Research Scientist at the National Center for Supercomputing Applications and an Assistant Professor of Astronomy at the University of Illinois, Urbana-Champaign. He has published several books and a number of articles and tutorials on a range of topics. You can reach him at rb@ncsa.uiuc.edu.
Table of contents
Local resources
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
