6.2
The Subquery in an UPDATE statement
The Subquery in an UPDATE statement
- Subqueries may be used in an UPDATE statement
- Since it is possible to change many values at once with a subquery, take special care before running an UPDATE statement with a subquery. You might make a copy of the table and data you are trying to change to test with before running your statement on live data.
- It is also possible to run your UPDATE statement inside of a transaction block that allows you to ROLLBACK or undo a statement. We will address the topic of ROLLBACK in a future lesson.
Code Sample:
1 UPDATE country
2 SET GNPOld = 0.00
3 WHERE Code IN
4 (SELECT CountryCode FROM countrylanguage WHERE population = 0)
Results:
UPDATE country
- Update the country table
SET GNPOld = 0.00
- Set the value of the GNPOld table = 0.00.
- No quotes are required because the GNPOld column is a decimal datatype
WHERE Code IN
- Update only the rows where the Code column value is in the results list returned in the subquery show below.
(SELECT CountryCode FROM countrylanguage WHERE population = 0)
- Return a list of values from the CountryCode column from the countrylanguage table that has a population equal to zero.
- If these values match a code in the country table, the row is updated.