Database Reference
In-Depth Information
15.4.2
Updating Many Rows
There are three rows with the name and country of Sheryl Crow because
there are three of her CDs in the table we created. Update all three at once,
changing her country to Canada.
UPDATE TESTMUSICCD SET ARTIST_COUNTRY='Canada'
WHERE ARTIST_NAME = 'Sheryl Crow';
SQL*Plus Worksheet will reply, “3 rows updated.”
Another method of updating data is to use subqueries. For example, let's
say you want to update ARTIST_COUNTRY column values in TEST-
MUSICCD with data from the ARTIST table. You can use a correlated
subquery to match the artist's name between the ARTIST and TESTMUS-
ICCD tables to find the country. The following query removes the changes
to countries of residence for both Jewel and Sheryl Crow.
UPDATE TESTMUSICCD T SET ARTIST_COUNTRY=
(SELECT COUNTRY FROM ARTIST A
WHERE A.NAME = T.ARTIST_NAME);
SQL*Plus Worksheet will reply, “13 rows updated.”
Note:
Updated rows must comply with any constraints defined for a table.
If one row does not comply, all rows updated by the statement are automat-
ically rolled back.
You can also update more than one column, whether you are updating
one row or many rows. For example, change the title and the country with
one update command. In the next example, we change the
ARTIST_NAME column of each TESTMUSICCD table row to uppercase
using a function, and change the PRESSED_DATE using a correlated sub-
query that finds the most recent RECORDING_DATE from the songs on
the CD (TESTMUSICCD table). You also use a WHERE clause in the
UPDATE command so that you only update Sheryl Crow's three rows.
UPDATE TESTMUSICCD T
SET ARTIST_NAME=UPPER(ARTIST_NAME),
PRESSED_DATE = (SELECT MAX(RECORDING_DATE)
