SQL Series...39

UPDATE, DELETE, and INSERT statements

In addition to SELECT statements, UPDATE, DELETE, and INSERT statements can also include WHERE clauses. Those WHERE clauses can contain subqueries in the same way that SELECT statement WHERE clauses do.

For example, Zetec has just made a volume purchase deal with Olympic Sales and wants to retroactively provide Olympic with a 10-percent credit for all its purchases in the last month. You can give this credit with an UPDATE statement:

UPDATE TRANSMASTER

SET NetAmount = NetAmount * 0.9

WHERE CustID =

(SELECT CustID

FROM CUSTOMER

WHERE Company = ‘Olympic Sales’) ;

You can also have a correlated subquery in an UPDATE statement. Suppose the CUSTOMER table has a column LastMonthsMax, and Zetec wants to give such a credit for purchases that exceed LastMonthsMax for the customer:

UPDATE TRANSMASTER TM

SET NetAmount = NetAmount * 0.9

WHERE NetAmount >

(SELECT LastMonthsMax

FROM CUSTOMER C

WHERE C.CustID = TM.CustID) ;

Note that this subquery is correlated: The WHERE clause in the last line refer-ences both the CustID of the CUSTOMER row from the subquery and the CustID of the current TRANSMASTER row that is a candidate for updating.

A subquery in an UPDATE statement can also reference the table that is being updated. Suppose that Zetec wants to give a 10-percent credit to customers whose purchases have exceeded $10,000:

UPDATE TRANSMASTER TM1

SET NetAmount = NetAmount * 0.9

WHERE 10000 < (SELECT SUM(NetAmount)

FROM TRANSMASTER TM2

WHERE TM1.CustID = TM2.CustID);

The inner subquery calculates the SUM of the NetAmount column for all TRANSMASTER rows for the same customer. What does this mean? Suppose that the customer with CustID = 37 has four rows in TRANSMASTER with values for NetAmount: 3000, 5000, 2000, and 1000. The SUM of NetAmount for this CustID is 11000.

The order in which the UPDATE statement processes the rows is defined by your implementation and is generally not predictable. The order may differ depending on how the rows are arranged on the disk. Assume that the implementation processes the rows for this CustID in this order: first the TRANSMASTER with a NetAmount of 3000, then the one with NetAmount = 5000, and so on. After the first three rows for CustID 37 have been updated, their NetAmount values are 2700 (90 percent of 3000), 4500 (90 percent of 5000), and 1800 (90 percent of 2000). Then when you process the last TRANSMASTER row for CustID 37, whose NetAmount is 1000, the SUM returned by the subquery would seem to be 10000 — that is, the SUM of the new NetAmount values of the first three rows for CustID 37, and the old NetAmount value of the last row for CustID 37. Thus it would seem that the last row for CustID 37 isn’t updated, because the comparison with that SUM is not True (10000 is not less than SELECT SUM (NetAmount)). But that is not how the UPDATE statement is defined when a subquery references the table that is being updated. All evaluations of subqueries in an UPDATE state-ment reference the old values of the table being updated. In the preceding UPDATE for CustID 37, the subquery returns 11000 — the original SUM.

The subquery in a WHERE clause operates the same as a SELECT statement or an UPDATE statement. The same is true for DELETE and INSERT. To delete all of Olympic’s transactions, use this statement:

DELETE TRANSMASTER

WHERE CustID =

(SELECT CustID

FROM CUSTOMER

WHERE Company = ‘Olympic Sales’) ;

As with UPDATE, DELETE subqueries can also be correlated and can also refer-ence the table being deleted. The rules are similar to the rules for UPDATE subqueries. Suppose you want to delete all rows from TRANSMASTER for cus-tomers whose total NetAmount is larger than $10,000:

DELETE TRANSMASTER TM1

WHERE 10000 < (SELECT SUM(NetAmount)

FROM TRANSMASTER TM2

WHERE TM1.CustID = TM2.CustID) ;

This query deletes all rows from TRANSMASTER that have CustID 37, as well as any other customers with purchases exceeding $10,000. All references to TRANSMASTER in the subquery denote the contents of TRANSMASTER before any deletes by the current statement. So even when you are deleting the last TRANSMASTER row for CustID 37, the subquery is evaluated on the original TRANSMASTER table and returns 11000.

When you update, delete, or insert database records, you risk making a table’s data inconsistent with other tables in the database. Such an inconsistency is called a modification anomaly, discussed in Chapter 5. If you delete TRANSMASTER records and a TRANSDETAIL table depends on TRANSMASTER, you must delete the corresponding records from TRANSDETAIL, too. This operation is called a cascading delete, because

the deletion of a parent record must cascade to its associated child records. Otherwise, the undeleted child records become orphans. In this case, they would be invoice detail lines that are in limbo because they are no longer connected to an invoice record.

INSERT can include a SELECT clause. A use for this statement is filling “snap-shot” tables. For a table with the contents of TRANSMASTER for October 27, do this:

CREATE TABLE TRANSMASTER_1027

(TransID INTEGER, TransDate DATE,

...) ;

INSERT INTO TRANSMASTER_1027

(SELECT * FROM TRANSMASTER

WHERE TransDate = 2003-10-27) ;

Or you may want to save rows only for large NetAmounts:

INSERT INTO TRANSMASTER_1027

(SELECT * FROM TRANSMASTER TM

WHERE TM.NetAmount > 10000

AND TransDate = 2003-10-27) ;