更新查询更改多列中的值

Question

How can I write a query to change a value in multiple columns where there is a specific value? I want to update a table: look through 40 columns (q1-q40) if the value in that column is -1 then change that to -999 for all rows. Do i still have to list cases for each column or is there an easier way?

Thanks!

Answer 1

You can't escape the long winded approach.

If I understand your requirement, it would be something like...

UPDATE
  table
SET
  col00 = CASE WHEN col00 = -1 THEN -999 ELSE col00 END,
  col01 = CASE WHEN col01 = -1 THEN -999 ELSE col01 END,
  col02 = CASE WHEN col02 = -1 THEN -999 ELSE col02 END,
  ...
  col37 = CASE WHEN col37 = -1 THEN -999 ELSE col37 END,
  col38 = CASE WHEN col38 = -1 THEN -999 ELSE col38 END,
  col39 = CASE WHEN col39 = -1 THEN -999 ELSE col39 END
WHERE
  -1 IN (col00, col01, col02, ... col37, col38, col39)

Or 40 straight forward updates...

This is a good example of when code that writes SQL is a useful approach.