Doing More with MySQL - Learning MySQL - page 293

mysql> REPLACE artist SET artist_id = 2,
-> artist_name = "Nick Cave and The Bad Seeds";
Query OK, 2 rows affected (0.00 sec)
The first variant is almost identical to our previous example, except it includes the
optional INTO keyword (which, arguably, improves the readability of the statement).
The second variant explicitly lists the column names that the matching values should
be inserted into. The third variant is the same as the second, without the optional
INTO keyword. The final variant uses the SET syntax; you can add the optional keyword
INTO to this variant if you want. Note that if you don't specify a value for a column, it's
set to its default value, just like for INSERT .
You can also bulk-replace into a table, removing and inserting more than one row.
Here's an example:
mysql> REPLACE artist (artist_id, artist_name)
-> VALUES (2, "Nick Cave and The Bad Seeds"),
-> (3, "Miles Dewey Davis");
Query OK, 4 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0
Note that four rows are affected: two deletions and two insertions. You can also see
that two duplicates were found, meaning the replacement of existing rows succeeded.
In contrast, if there isn't a matching row in a REPLACE statement, it acts just like an
INSERT :
mysql> REPLACE INTO artist VALUES (10, "Jane's Addiction");
Query OK, 1 row affected (0.22 sec)
You can tell that only the insert occurred, since only one row was affected.
Replacing also works with a SELECT statement. Recall the shuffle table from “Inserting
Data Using Queries,” at the beginning of this chapter. Suppose you've added 10 tracks
to it, but you don't like the choice of the seventh track in the playlist. Here's how you
can replace it with a random choice of another track:
mysql> REPLACE INTO shuffle (artist_id, album_id, track_id, sequence_id)
-> SELECT artist_id, album_id, track_id, 7 FROM
-> track ORDER BY RAND() LIMIT 1;
Query OK, 2 rows affected (0.01 sec)
Records: 1 Duplicates: 1 Warnings: 0
Again, the syntax is the same as with INSERT , but a deletion is attempted (and succeeds!)
before the insertion. Note that we keep the value of the sequence_id as 7.
If a table doesn't have a primary key, replacing doesn't make sense. This is because
there's no way of uniquely identifying a matching row in order to delete it. When you
use REPLACE on such a table, its behavior is identical to INSERT . Also, as with INSERT ,
you can't replace rows in a table that's used in a subquery. Finally, note the difference
between INSERT IGNORE and REPLACE : the first keeps the existing data with the duplicate
key and does not insert the new row, while the second deletes the existing row and
replaces it with the new one.