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
- 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
updating, you can use
replace: it deletes all related data too);
- When triggers need to fire, and you expect an
insert(bad reason, okay).