REPLACE versus INSERT in SQL

by

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

Leave a Comment