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.

graphics/book.gif

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.



Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
ISBN: 067232489X
EAN: 2147483647
Year: 2005
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net