-> SET album_name = UPPER(album_name)

• You can’t use
ORDER
BY
• You can’t use LIMIT You can’t update a table that’s read from in a nested subquery.
Other than that, multiple-table updates are much the same as single-table ones.
Replacing Data
You’ll sometimes want to overwrite data. You can do this in two ways using the techniques we’ve shown previously Delete an existing row using its primary key and then insert anew replacement with the same primary key Update a row using its primary key, replacing some or all of the values (except the primary key).
The
REPLACE
statement gives you a third, convenient way to change data. This section explains how it works.
The
REPLACE
statement is just like INSERT, but with one difference. You can’t
INSERT
a new row if there is an existing row in the table with the same primary key, You can get around this problem with a
REPLACE
query, which first removes any existing row with the same primary key and then inserts the new one.
Let’s try an example, where we’ll replace the row for "Nick Cave & The Bad Seeds ":
mysql> REPLACE artist VALUES (2, "Nick Cave and The Bad Seeds");
Query OK, 2 rows affected (0.02 sec)
You can see that MySQL reports that two rows were affected first, the old row was deleted, and, second, the new row was inserted. You can see that the change we made was minor—we just changed the & to an “and”—and therefore, it could easily have been accomplished with an UPDATE. Because the tables in the music database contain few columns, it’s difficult to illustrate an example in which
REPLACE
looks simpler than
UPDATE
You can use the different
INSERT
syntaxes with REPLACE, including using
SELECT
queries.
Here are some examples:
mysql> REPLACE INTO artist VALUES (2, "Nick Cave and The Bad Seeds");
Query OK, 2 rows affected (0.00 sec)
mysql> REPLACE INTO artist (artist_id, artist_name)

Download 4.24 Mb.

Share with your friends: