Is it possible to perform multiple updates with a single UPDATE SQL statement?

Question

Let's say I have a table tbl with columns id and title. I need to change all values of title column:

1. from 'a-1' to 'a1',
2. from 'a.1' to 'a1',
3. from 'b-1' to 'b1',
4. from 'b.1' to 'b1'.

Right now, I'm performing two UPDATE statements:

UPDATE tbl SET title='a1' WHERE title IN ('a-1', 'a.1') UPDATE tbl SET title='b1' WHERE title IN ('b-1', 'b.1') 

This isn't at all a problem, if the table is small, and the single statement completes in less than a second and you only need a few statements to execute.

You probably guested it - I have a huge table to deal with (one statement completes in about 90 seconds), and I have a huge number of updates to perform.

So, is it possible to merge the updates so it would only scan the table once? Or perhaps, there's a better way to deal with in a situation like this.

EDIT: Note, that the real data I'm working with and the changes to the data I have to perform are not really that simple - the strings are longer and they don't follow any pattern (it is user data, so no assumptions can be made - it can be anything).

Answer 1

You can use one statement and a number of case statements

update tbl   set title =      case       when title in ('a-1', 'a.1') then 'a1'       when title in ('b-1', 'b.1') then 'b1'       else title     end 

Of course, this will cause a write on every record, and with indexes, it can be an issue, so you can filter out only the rows you want to change:

update tbl   set title =      case       when title in ('a-1', 'a.1') then 'a1'       when title in ('b-1', 'b.1') then 'b1'       else title     end where   title in ('a.1', 'b.1', 'a-1', 'b-1') 

That will cut down the number of writes to the table.