REPLACE versus INSERT in SQL

Question

I am doing the following SQL tutorial: http://sql.learncodethehardway.org/book/ex11.html

and in this exercise the author says in the second paragraph:

In this situation, I want to replace my record with another guy but keep the unique id. Problem is I'd have to either do a DELETE/INSERT in a transaction to make it atomic, or I'd need to do a full UPDATE.

Could anyone explain to me what the problem is with doing an UPDATE, and when we might choose REPLACE instead of UPDATE?

The UPDATE code:

UPDATE person SET first_name = "Frank", last_name = "Smith", age = 100
    WHERE id = 0;

Here is the REPLACE code:

REPLACE INTO person (id, first_name, last_name, age)
    VALUES (0, 'Frank', 'Smith', 100);

EDIT: I guess another question I have is why would you ever do a DELETE/INSERT instead of just an UPDATE as is discussed in the quoted section?

Solution 1

According to the documentation, the difference is:

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.

So what it does:

• Try to match the row using one of the available indexes;
• If the row doesn't exist already: add a new one;
• If the row exists already: delete the existing row and add a new one afterwards.

When might using this become useful over separate insert and update statements?

• You can safely call this, and you don't have to worry about existing rows (one statement vs. two);
• If you want related data to be removed when inserting / updating, you can use replace: it deletes all related data too);
• When triggers need to fire, and you expect an insert (bad reason, okay).

Solution 2

First Replace isn't widely understood in all database engines.

Second replace inserts/updates a record based on the primary key. While with update you can specify more elaborate conditions:

UPDATE person SET first_name = 'old ' + first_name WHERE age > 50

Also UPDATE won't create records.

Solution 3

UPDATE will have no effect if the row does not exist.

Where as the INSERT or REPLACE will insert if the row doesn't exists or replace the values if it does.

Solution 4

Update will change the existing records value in table based on particular condition. So you can change one or many records in single query.

Insert or Replace will insert a new record if records is not present in table else will replace. Replace will only work if and only if you provide the primary key value in the insert or replace query. If you forget to add primary key field value than a new record will created in table.

Case example:-

Update: You have a calculation of wages to be done based on a formula using the column values. In this case you will always use update query as using one single query you can update multiple records.

Insert or Replace: Already mentioned in the link you shared.

View more solutions