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.

CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.
End-of-Chapter Survey
: How would you rate the overall quality of this chapter?- Very Low Quality
- Low Quality
- Moderate Quality
- High Quality
- Very High Quality