JavaScript Editor Javascript source editor     Web programming 

Team LiB
Previous Section Next Section

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.

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

    Team LiB
    Previous Section Next Section

    JavaScript Editor Javascript source editor     Web programming