Monday, January 01, 2007

Why can't MySQL use UPDATE instead of DELETE and INSERT for REPLACE?

"[REPLACE] either inserts, or deletes and inserts."


from "REPLACE Syntax"

My question is why it is done like this? Why not simply UPDATE the row? Wouldn't it be a lot less taxing?

7 comments:

Anonymous said...

... ON DUPLICATE KEY UPDATE SET mycol=VALUES(mycol) !

burtonator said...

You should see the

INSERT
...
ON DUPLICATE KEY UPDATE

Syntax instead of REPLACE..... REPLACE is evil.

Anonymous said...

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.

Anonymous said...

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)

Anonymous said...

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% :)

Frank said...

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

Anonymous said...

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