Not to play the smartass here, but the answer is in the man page you linked:
For another MySQL extension to standard SQL — that either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”. INSERT ... ON DUPLICATE KEY UPDATE is available as of MySQL 4.1.0.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
And because it's easier, try to insert, fail for duplicate key, then insert. It doesn't know if "replace" will always match an existing row (and knowing this should make you use an update, no need for replace)
IMO it's a bet for db: if the chances to not have a dup key is higher then 50% is better the insert (and on failure due to dup key the update); else the update (and on failure the insert)
Maybe statistics says that the chances on average are above 50% :)
7 comments:
... ON DUPLICATE KEY UPDATE SET mycol=VALUES(mycol) !
You should see the
INSERT
...
ON DUPLICATE KEY UPDATE
Syntax instead of REPLACE..... REPLACE is evil.
CREATE TABLE t1 (
a INT,
b INT,
UNIQUE (a),
UNIQUE (b)
);
INSERT INTO t1 VALUES (1,1), (2,2);
REPLACE INTO t1 VALUES (1,2);
This type of query would not work with an update.
Not to play the smartass here, but the answer is in the man page you linked:
For another MySQL extension to standard SQL — that either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”. INSERT ... ON DUPLICATE KEY UPDATE is available as of MySQL 4.1.0.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
And because it's easier, try to insert, fail for duplicate key, then insert. It doesn't know if "replace" will always match an existing row (and knowing this should make you use an update, no need for replace)
IMO it's a bet for db: if the chances to not have a dup key is higher then 50% is better the insert (and on failure due to dup key the update); else the update (and on failure the insert)
Maybe statistics says that the chances on average are above 50% :)
Thank you Tobias for pointing out the reason.
Thank you for the "ON DUPLICATE KEY" as well. I knew the workaround, but wanted to know the reason behind why DELETE followed by an UPDATE was used.
Frank
Frank,
The reason it is not UPDATE is because it does not read the old row but simply replaces it which is performance gain in some cases.
As other mentioned Insert on dup key update is what you can use if you want update
Post a Comment