Hack 3. Perform Conditional INSERTs

The humble INSERT statement is a masterpiece of declarative language design. With only two main variations, it can handle a host of different behaviors.

You can use INSERT INTO table ( list ) VALUES ( list ) to add a single row to a table. You can also use INSERT INTO table ( list ) SELECT stmt to insert several rows.

1.3.1. INSERT ... VALUES

You can include expressions and literal expressions in the VALUES list.

Suppose you want to record the fact that member jim01 has borrowed the book bk002 from your library. This book is due back in 14 days. Add the number 14 to today's date to get the due date:

INSERT INTO libraryLoan(member,book,dueDate) VALUES ('jim01', 'bk002', CURRENT_DATE + 14);

The VALUES list can include more complex calculations, and these calculations may involve subqueries. Let's say that when the book is returned you must impose a fine of 20 cents if the book is overdue. You can use a single INSERT statement to apply this fine:

INSERT INTO libraryReturn(member,book,returnDate,fine) VALUES ('jim01','bk002',CURRENT_DATE, (SELECT 0.20 fine FROM libraryLoan WHERE member='jim01' AND book='bk002' GROUP BY member, book HAVING MAX(dueDate)

The SELECT statement deserves some explanation. SELECT will return either a single row with the number 0.20, or no rows. If no rows are returned, a NULL will be put in the fine column for the new libraryReturn row.

Let's take this statement one step at a time. First, look at the loan records for this borrower and this book:

mysql> SELECT member, book, dueDate -> FROM libraryLoan -> WHERE member='jim01' AND book='bk002'; +--------+-------+------------+ | member | book | dueDate | +--------+-------+------------+ | jim01 | bk002 | 2005-03-22 | | jim01 | bk002 | 2005-09-21 | | jim01 | bk002 | 2006-07-28 | +--------+-------+------------+

Borrower jim01 really loves that book; he's borrowed it three times! But you are interested in only the most recent lending, so you use a GROUP BY with MAX to get the one record of interest:

mysql> SELECT member, book, MAX(dueDate) -> FROM libraryLoan -> WHERE member='jim01' AND book='bk002' -> GROUP BY member, book; +--------+-------+--------------+ | member | book | MAX(dueDate) | +--------+-------+--------------+ | jim01 | bk002 | 2006-07-28 | +--------+-------+--------------+

Now you can be sure that at most, one row will be returned. A returned row will generate a fine only if dueDate was prior to today's date. You can use a HAVING clause to filter the result of a GROUP BY. Also, the important data is the fine. There is no need for the other values in the SELECT clause. What you actually need is the value of the fine:

mysql> SELECT 0.20 fine -> FROM libraryLoan -> WHERE member='jim01' AND book='bk002' -> GROUP BY member, book -> HAVING MAX(dueDate) Empty set (0.00 sec)

jim01 escapes a fine because the due date is today or some time in the future. However, ann02 is returning book bk005 late and she is going to have to pay:

mysql> SELECT 0.20 fine -> FROM libraryLoan -> WHERE member='ann02' AND book='bk005' -> GROUP BY member, book -> HAVING MAX(dueDate) +------+ | fine | +------+ | 0.20 | +------+

1.3.2. INSERT ... SELECT

You can use the INSERT ... SELECT statement to copy data from one table to another, but it has other uses as well. For instance, you can use it to insert a single row as an alternative to the VALUES option. In MySQL and SQL Server, you can omit the FROM clause to get a single row result. These two statements are equivalent:

INSERT INTO roomBooking(whn,wht,who) VALUES ('2006-07-13','Ballroom','Col. Mustard'); INSERT INTO roomBooking(whn,wht,who) SELECT '2006-07-13','Ballroom','Col. Mustard';

This approach is fine if you always want to insert the row. But suppose you want to insert the booking only if the room is free. That means you want the SELECT statement to return one row if the room is free and zero rows if the room is occupied.

Look at the line that shows "rows affected" in the following two attempts at booking. Prof. Plum's booking is successful and one row is added. Miss Scarlet's booking results in zero rows being added because Col. Mustard has already booked the ballroom on that date:

mysql> INSERT INTO roomBooking(whn,wht,who) -> SELECT DATE '2006-07-13','Billiard Room','Prof. Plum' -> FROM dual -> WHERE NOT EXISTS (SELECT who FROM roomBooking -> WHERE whn = DATE '2006-07-13' -> AND wht='Billiard room'); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO roomBooking(whn,wht,who) -> SELECT DATE '2006-07-13','Ballroom','Miss Scarlet' -> FROM dual -> WHERE NOT EXISTS (SELECT who FROM roomBooking -> WHERE whn = DATE '2006-07-13' -> AND wht='Ballroom'); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

The first statement adds a new row to the roomBooking table. In Miss Scarlet's booking, the clause WHERE whn = DATE '2006-07-13' AND wht='Ballroom' matched Col. Mustard's booking, so the NOT EXISTS expression filtered out all results. As a result, the SELECT clause returns zero rows and the INSERT does nothing.

The queries work without the dual table in PostgreSQL:

INSERT INTO roomBooking(whn,wht,who) SELECT DATE '2006-07-13','Billiard Room','Prof. Plum' WHERE NOT EXISTS (SELECT who FROM roomBooking WHERE whn = DATE '2006-07-13' AND wht='Ballroom')

In SQL Server, you leave out the word DATE and don't need to reference dual:

INSERT INTO roomBooking(whn,wht,who) SELECT '2006-07-13','Billiard Room','Prof. Plum' WHERE NOT EXISTS (SELECT who FROM roomBooking WHERE whn = '2006-07-13' AND wht='Billiard room')