Why have REPLACE INTO and INSERT … ON DUPLICATE KEY UPDATE?
January 17, 2007 – 9:44 pmREPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong.
Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.
Yes, I realize both are MySQL additions, so please don’t bother telling me they’re not ANSI standard.
References:
4 Responses to “Why have REPLACE INTO and INSERT … ON DUPLICATE KEY UPDATE?”
Purely hypothetical, but…
CREATE TABLE foo (
mykey INT PRIMARY KEY,
cola CHAR(1) DEFAULT ‘A’,
colb CHAR(2) DEFAULT ‘B’
);
INSERT INTO foo (mykey, cola, colb)
VALUES (1, ‘X’, ‘Y’), (2, ‘X’, ‘Y’);
INSERT INTO foo (mykey, cola) VALUES (1, ‘Z’) ON DUPLICATE KEY UPDATE cola=’Z';
REPLACE INTO foo (mykey, cola) VALUES (2, ‘Z’);
We should now have (1, ‘Z’, ‘Y’), (2, ‘Z’, ‘B’). Of course, this could still be done with UPDATE by appending colb=DEFAULT. The maintainability of that would decrease though. Not that relying on REPLACE to set fields back to their default values is very maintainable either.
Real world case? Nope, can’t think of one either.
By Jason Stubbs on Jan 17, 2007
REPLACE is dangerous with InnoDB and cascading DELETEs if something references that row. It will also change the auto_increment of the record.
ON DUPLICATE also allows for more freedom because, while you may opt to update with the values of the INSERT clause you are not tied down to that. You can use any kind of complex expression in order to achieve different results upon INSERT or UPDATE
FYI update is not always faster! sometimes a DELETE followed by an INSERT may actually be faster.
I use DUPLICATE very often, yet I have yet to find use for REPLACE in an environment that enforces referential integrity
By Guillermo Roditi on Jan 17, 2007
Think you don’t have a primary key and want to replace a tuple which might exist (or not) in that table …
By pabloj on Jan 18, 2007
They’re not equivalent; ODKU allows you to specify exactly which fields get updated, where REPLACE wipes out all existing data and makes a new row. Sometimes you want this, for example when you have a timestamp column and don’t want to explicitly set it. Most queries can be written with either syntax, but in practice sometimes I find one or the other solves the problem better. I generally do not use REPLACE.
By Xaprb on Jan 18, 2007