REPLACE versus INSERT in SQL

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).

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.

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.