Doing More with MySQL - Learning MySQL - page 292

• 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 tech-
niques we've shown previously:
• Delete an existing row using its primary key and then insert a new 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)
-> VALUES (2, "Nick Cave and The Bad Seeds");
Query OK, 2 rows affected (0.00 sec)
mysql> REPLACE artist (artist_id, artist_name)
-> VALUES (2, "Nick Cave and The Bad Seeds");
Query OK, 2 rows affected (0.01 sec)