Clever Geek Handbook

Update table with multiple SET arguments

I have been provided with a list of changes that need to be made to the table. I was wondering how it would be possible to make all the changes in one script ... I tried the following

UPDATE tableA
SET col1 = 'somedata' WHERE col2 = 'somereference'
SET col1 = 'someotherdata' WHERE col2 = 'someotherreference'
SET col1 = 'evenmoredata' WHERE col2 = 'anotherreference'

but it doesn't work. Is there some specific syntax I can use to achieve this, or am I sticking with this: -

UPDATE tableA
SET col1 = 'somedata' WHERE col2 = 'somereference'

UPDATE tableA
SET col1 = 'someotherdata' WHERE col2 = 'someotherreference'

for every change i want to make?

+3


source to share


1 answer


Use a case statement in a statement for one set:

UPDATE tableA
SET col1 = case col2
    when 'somereference' then 'somedata'
    when 'someotherreference' then 'someotherdata'
    when 'anotherreference' then 'evenmoredata'
    else col1
    end


It's a good idea to put the default on the original, no matter where you are using the wrong lights (and you must use a where clause, otherwise you will update all lines)

+1


source






More articles:


All Articles