Using the REPLACE CommandAnother 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 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. |