The REPLACE Statement

The REPLACE statement works just like INSERT, except that if a row already exists in the table with the same PRIMARY KEY value as the new data, the new row replaces it. Therefore, REPLACE never causes a PRIMARY KEY violation.

The syntax for REPLACE is just like INSERT, or you can use the alternative syntax with SET, if you prefer. The following examples both perform the same operation: to replace the MINI product with a new description, weight, and price.

 mysql> REPLACE INTO products     -> (product_code, name, weight, price)     -> VALUES ('MINI', 'Mini produt', '1.25', 3.99); Query OK, 2 rows affected (0.00 sec) 

 mysql> REPLACE INTO products     -> SET product_code = 'MINI',     ->     name = 'Mini product',     ->     weight = 1.25,     ->     price = 3.99; Query OK, 2 rows affected (0.00 sec) 

Replacing Rows

The actual behavior of REPLACE is to execute a DELETE operation with the given primary key value and then perform an INSERT; it does not perform an UPDATE.

The output from the previous examples shows two rows affected because two operations took place: one DELETE and one INSERT.

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: