CODEWITHSUNDEEP
mysqlcommand
Luis Alvarado
Luis Alvarado

Reputation: 9406

In MYSQL in what scenarios should I use REPLACE and in which ones should I use UPDATE

In MySQL, in which cases should I use REPLACE and in which ones should I use UPDATE. Adding to this as a bonus, in what cases should I use a combination of DELETE and then INSERT in comparison to the 2 first mentioned.

Upvotes: 0

Views: 89

Answers (2)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385295

From the documentation:

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—see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

Best not be trying to write portable SQL, then.

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.

So there's another case in which you shouldn't use it.

To use REPLACE, you must have both the INSERT and DELETE privileges for the table.

That's not the same as having UPDATE privileges.

There is also a whole bunch of advisories and cautions in the comments.

Why does nobody read documentation any more? It's hard enough bothering to keep it up-to-date when you know that people do use it.

Upvotes: 1

Ben English
Ben English

Reputation: 3928

You use REPLACE when you are not sure if a record exists in your database or if you do not want to check if it exists. Basically what will happen is if the record exists it will be deleted and inserted, if it does not it will be inserted. It's there mostly for convenience but is not ANSI SQL.

It's generally better for performance to do an UPDATE vs a DELETE/INSERT. So it's generally discouraged to use REPLACE.

Your preference should always be

1. UPDATE
2. DELETE/INSERT (REPLACE)

Upvotes: 1

Related Questions