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. By the Way 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 replaces 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 was 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.50 | 1000 | | 2 | Bunches of Grapes | Seedless grapes. | 2.99 | 500 | | 3 | Bottled Water (6-pack) | 500ml spring water. | 2.29 | 250 | | 4 | Bottled Water (12-pack) | 500ml spring water. | 4.49 | 500 | | 5 | Bananas | Bunches, green. | 1.99 | 150 | | 6 | Pears | Anjou, nice and sweet. | 0.5 | 500 | | 7 | Avocado | Large Haas variety. | 0.99 | 750 | +----+-------------------------+-------------------------+------------+----------+ 7 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. |