Learning MySQL

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 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 ...