Hack 3. Perform Conditional INSERTs

Table of contents:

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);

In SQL Server, you must use the function Getdate( ) in place of CURRENT_DATE. For Access, you can use Date( ).

You might prefer to use the ANSI standard method in your database. With the ANSI method you use the phrase CURRENT_DATE + INTERVAL '14' DAY in place of CURRENT_DATE+14. Oracle, PostgreSQL, and MySQL will allow that.

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)

SQL Server does not allow a SELECT statement inside the VALUES list. Instead, you can use this (see the following section, "INSERT ... SELECT," for more details):

INSERT INTO libraryReturn(book,member,returnDate,fine) SELECT 'jim01','bk002',GetDate( ), (SELECT 0.20 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';

 

In Oracle, you can do the same thing, but you need to reference the dual table. Also, Oracle insists that you use the ANSI standard DATE keyword. Turn to [Hack #19] to see samples of date literals that you may use on each of the popular engines. Here's an example that attempts to book the ballroom for a customer named Col. Mustard on July 13, 2006:

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

MySQL also has the dual table. You do not usually need it, but you must reference dual if your SELECT statement includes a WHERE clause.

 

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')


Hack 4 UPDATE the Database

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net