Using the REPLACE Command

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.