REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts.
For another MySQL extension to standard SQL — that either inserts or updates — it should be use “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.
INSERT ... ON DUPLICATE UPDATE (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html)
Monday, November 9, 2009
REPLACE vs. INSERT ... ON DUPLICATE UPDATE in MySQL
Posted by J. Ernesto Aneiros at 1:40 PM
Subscribe to: Post Comments (Atom)
Post a Comment