Using the 'REPLACE' Command

Another method for modifying records is to use the REPLACE command, which is remarkably similar to the INSERT command.

REPLACE INTO table_name (column list) VALUES (column values);

The REPLACE statement works like this: if the record you are inserting into the table contains a primary key value that matches a record already in the table, the record in the table will be deleted and the new record inserted in its place.

The REPLACE command is a MySQL-specific extension to ANSI SQL. This command mimics the action of a DELETE and re-INSERT of a particular record. In other words, you get two commands for the price of one.

Using the grocery_inventory table, the following command will replace the entry for Apples:

mysql> replace into grocery_inventory values
    -> (1, 'Granny Smith Apples', 'Sweet!', '0.50', 1000);
Query OK, 2 rows affected (0.00 sec)

In the query result, notice that the result states, "2 rows affected". In this case, because id is a primary key that had a matching value in the grocery_inventory table, the original row was deleted and the new row inserted?2 rows affected.

Select the records to verify that the entry is correct, which it is

mysql> select * from grocery_inventory;
+----+-----------------------+-----------------------+-----------+-----------+
| id | item_name             | item_desc             | item_price | curr_qty |
+----+-----------------------+-----------------------+------------+----------+
|  1 | Granny Smith Apples   | Sweet!                |        0.5 |     1000 |
|  2 | Bunches of Grapes     | Seedless grapes.      |       2.99 |      500 |
|  3 | Bottled Water (6-pack)| 500ml spring water.   |       2.29 |      250 |
|  4 | Bananas               | Bunches, green.       |       1.99 |      150 |
|  5 | Pears                 | Anjou, nice and sweet.|        0.5 |      500 |
|  6 | Avocado               | Large Haas variety.   |       0.99 |      750 |
+----+-----------------------+-----------------------+------------+----------+
6 rows in set (0.00 sec)

If you use a REPLACE statement, and the value of the primary key in the new record does not match a value for a primary key already in the table, the record would simply be inserted and only one row would be affected.