What is the benefit of update instead of doing delete and then Insert in the same table.

Question

Hi, I have a table say example "ABC", I have a row that needs to be stored in to this "ABC" table. I plan to update it instead of doing delete from the table and then insert. What is the impact that this will make on database? In, table level, page level, time, cost and every thing. thanks Thiru

Answer 1

I'm not 100% certain what you're asking, but I'll take a shot in the dark. Doing a DELETE and then an INSERT into a table to update information is a Very Bad Idea.

Why? Because if you have another table with a foreign key referencing ABC, you will lose your reference. That is, of course, unless you set the PK of the new record with the same PK as the old (deleted) record. In which case, why didn't you just UPDATE in the first place?

Additionally, DELETING and then INSERTing is two operations whereas UPDATEing is one, making the DELETE and INSERT take (theoretically) more time.

There's also the ease-of-use factor. If you DELETE then INSERT, you have to manually keep track of every column value. If you UPDATE, you just need to know what you want to change.

Answer 2

Update is the easy and the fastest way to update a table.

Answer 3

An update will change the existing row which is a simple operation while a delete followed by an insert will be more work for the database.

In addition delete and insert may destroy references to your rows if you use identity columns.

Edit: Actually there are times where you will want to do a delete and insert instead of an update. It could be to simplify your logic if you don't know if the data to be updated exists. Then it would be easier to perform a delete and then doing an insert than to check if the row exists and then doing either an update or an insert based on the result of the check.

In addition you may want any identity columns to be updated depending on how you use it (if you use it to keep track of the latest changes to a table for instance).

But in general, doing an update is less work than a delete and insert operation so you should always use it if you can.

Answer 4

The key advantage of using UPDATE instead of DELETE/INSERT is that you can choose to only update the changed fields in the row (which is significantly faster than a DELETE followed by an INSERT).

Answer 5

Also one of the benefits of UPDATE is in the actual definition of an ACID database - Atomicity.

Answer 6

The DELETE & INSERT operations takes longer time compare to UPDATE.Think if u want to change only one column in the row.Then DELETE and INSERT will be complicated.