SQL Antipatterns, Volume 1: alternative solution (page 213)

Community PragProg Customers
,
1

@billkarwin

ON page 213, the generate-update.sql code uses a single SELECT query to generate multiple UPDATE statements, which have to be executed then to finish the UPDATE job. Here is an alternative solution which capitalizes on a correlated subquery so as to do the UPDATE job directly :

UPDATE Inventory i
SET last_used = (SELECT MAX(usage_date) FROM ComputerUsage where inventory_id = i.inventory_id)
;