SQL replace vs. doing an insert or update statement

Question

Hi,

I was wondering what the advantages/disadvantages or doing a MySQL replace vs. doing either an update or insert statement are.

Basically,

doing

dao.replaceEntry(entry);

instead of:

if(existing){
     dao.insertEntry(entry);
} else {
     dao.updateEntry(entry);
}

Also, would it be misleading to call the dao.replaceEntry call dao.insertOrUpdate?

Answer 1

I would do insertOrUpdate instead of replace. As per Mysql's docs

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

In case the row exists it is performing a delete and then inserting a new row. Update is the better way to do it then delete and insert as the REPLACE is doing i.e., one operation vs. two.

One more issue I can think of is having triggers or cascade delete in the database. As in case of REPLACE you mind end up having related rows deleted as REPLACE will first delete and then insert if the row with the same ID already exists.