How to Use UPDATE, DELETE, and INSERT SQL Statements
In addition to SELECT statements, UPDATE, DELETE, and INSERT SQL statements can also include WHERE clauses. Those WHERE clauses can contain subqueries in the same way that SELECT statements’WHERE clauses do.
For example, Zetec has just made a volume purchase deal with Olympic Sales and wants to provide Olympic with a retroactive 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 SaleDate > (CurrentDate – 30) DAY AND 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 references 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 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 $3,000), 4500 (90 percent of $5,000), and 1800 (90 percent of $2,000). 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, 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 — after all, 10000 is not less than 10000. 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 statement reference the old values of the table— the ones that are 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 FROM TRANSMASTER WHERE CustID = (SELECT CustID FROM CUSTOMER WHERE Company = 'Olympic Sales') ;
As with UPDATE, DELETE subqueries can also be correlated and can also reference the table being deleted. The rules are similar to the rules for UPDATE subqueries. Suppose you want to delete all rows from TRANSMASTER for customers whose total NetAmount is larger than $10,000:
DELETE FROM 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’re 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. 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.
If your implementation of SQL doesn’t support cascading deletes, you must do the deletions yourself. In this case, delete the appropriate records from the child table before deleting the corresponding record from the parent. That way, you don’t have orphan records in the child table, even for a second.