Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 6

Modifying data and schemas

Along with creating a database schema and populating tables with data, being able to selectively modify data is one of the most important skills necessary for a database developer. This article teaches you how to selectively delete or update data in an existing table and how to modify the structure of an existing table. To perform data modifications on a more complex database schema, you'll learn about embedded subqueries, both scalar and table, with data update and data insert operations. You'll also find out how to delete and modify data in complex schemas using the Apache Derby database.

Share:

Robert Brunner, NCSA Research Scientist, Assistant Professor of Astronomy, University of Illinois, Urbana-Champaign

Robert J. BrunnerRobert 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.



15 August 2006

Also available in Russian Japanese

Introduction

Previous articles in this series have:

  • Introduced the Apache Derby database.
  • Introduced the ij tool.
  • 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:

  1. Have a working Apache Derby database installation, which was covered in the first article in this series.
  2. Be familiar with the Apache Derby ij command-line tool, which was covered in the second article in this series.
  3. Have a properly initialized Bigdog's Surf Shop example database, which was detailed in the fourth and fifth articles in this series.
  4. Be familiar with the basics of the SQL SELECT statement, 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.


Delete data

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.


Update data

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
FunctionDescription
CURRENT_DATEReturns the current date in a suitable Apache Derby DATE format
CURRENT_ISOLATIONReturns the current transaction isolation level, which will be discussed in more detail in a future article, as a two-character string
CURRENT_SCHEMAReturns the schema name as a string of up to 128 characters that is used to qualify unqualified database object names
CURRENT_TIMEReturns the current time in a suitable Apache Derby TIME format
CURRENT_TIMESTAMPReturns the current timestamp in a suitable Apache Derby TIMESTAMP format
CURRENT_USERReturns 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
OperatorExampleDescription
INitemNumber 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.
EXISTSEXISTS (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.
ALLitemNumber = 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.
ANYitemNumber = 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.


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 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.


Summary

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.

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Information Management
ArticleID=153038
ArticleTitle=Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 6
publish-date=08152006