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.

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