The Subquery in an UPDATE statement

6.2

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

Overall Quality: How would you rate the overall quality of this chapter?

Very Low Quality
Low Quality
Moderate Quality
High Quality
Very High Quality

Additional CommentsComments will be automatically submitted when you navigate away from the page.

Like this? Endorse it!