In SQL, is UPDATE always faster than DELETE+INSERT

Question

Say I have a simple table that has the following fields:

1. ID: int, autoincremental (identity), primary key
2. Name: varchar(50), unique, has unique index
3. Tag: int

I never use the ID field for lookup, because my application is always based on working with the Name field.

I need to change the Tag value from time to time. I'm using the following trivial SQL code:

UPDATE Table SET Tag = XX WHERE Name = YY;

I wondered if anyone knows whether the above is always faster than:

DELETE FROM Table WHERE Name = YY;
INSERT INTO Table (Name, Tag) VALUES (YY, XX);

Again - I know that in the second example the ID is changed, but it does not matter for my application.

Answer 1

Delete + Insert is almost always faster because an Update has way more steps involved.

Update:

1. Look for the row using PK.
2. Read the row from disk.
3. Check for which values have changed
4. Raise the onUpdate Trigger with populated :NEW and :OLD variables

5. Write New variables to disk (The entire row)

   (This repeats for every row you're updating)

Delete + Insert:

1. Mark rows as deleted (Only in the PK).
2. Insert new rows at the end of the table.

3. Update PK Index with locations of new records.

   (This doesn't repeat, all can be perfomed in a single block of operation).

Using Insert + Delete will fragment your File System, but not that fast. Doing a lazy optimization on the background will allways free unused blocks and pack the table altogether.

Answer 2

Keep in mind the actual fragmentation that occurs when DELETE+INSERT is issued opposed to a correctly implemented UPDATE will make great difference by time.

Thats why, for instance, REPLACE INTO that MySQL implements is discouraged as opposed to using the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax.