-> VALUES (2, "Nick Cave and The Bad Seeds")

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 fora 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 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)

Download 4.24 Mb.

Share with your friends: