3

I've been given a list of changes to make to a table. I was wondering how it would be possiblet to make all of the changes in one script...I've tried the following

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

but this doesn't work. Is there a specific syntax I can use to achieve this, or am I stuck with doing it like this:-

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

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

for each change I want to make?

Mat Richardson
  • 3,576
  • 4
  • 31
  • 56
  • 2
    use a case statement. asked in previous posts and answered: http://stackoverflow.com/questions/1547026/t-sql-conditional-update-v2 – xQbert Dec 15 '14 at 15:55

1 Answers1

1

Use a case statement in the single set statement:

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

Its a good idea to put the default in of the original value incase whatever where clause you're using mis-fires (and you should use the where clause anyway otherwise you'll update all rows)

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • 3
    Not just a good idea, essential I think. If you don't put the original value in `else`, it will actually `NULL` out the column for rows that don't match another condition. – Michael Berkowski Dec 15 '14 at 15:56
  • @MichaelBerkowski: It was more that I would assume that a where clause would be use to restrict the scope of the update, but as you say, if this doesn't match with the case statement exactly then you end up with null updates. – Jon Egerton Dec 15 '14 at 15:58
  • 3
    @MichaelBerkowski I would rather add a `where col1 in ('somereference', 'someotherreference', 'anotherreference')` to avoid unnecessary updates –  Dec 15 '14 at 15:58