CODEWITHSUNDEEP
sqlsql-servert-sqlcase-when
VPP
VPP

Reputation: 789

Update multiple columns using same SQL server case statement

I'm having the following UPDATE statement

UPDATE PAYMENT_HISTORY
SET request_status_id =
CASE
  WHEN PHP.request_status_id = PHP.prevRequest_status_id THEN NULL 
  ELSE PHP.request_status_id
END,
  is_changed =
CASE
  WHEN PHP.request_status_id = PHP.prevRequest_status_id THEN 0
  ELSE 1
END

Is it possible to update both columns using a single case statement ?

Upvotes: 0

Views: 9414

Answers (5)

bart
bart

Reputation: 1

Yes, it is possible by using a variable.

UPDATE PAYMENT_HISTORY
SET request_status_id =
CASE
  WHEN PHP.request_status_id = PHP.prevRequest_status_id 
    THEN coalesce((@ischanged:=0)+null,NULL) 
    ELSE coalesce((@ischanged:=1)+null,PHP.request_status_id)
  END,
  is_changed = @ischanged

In general:

UPDATE mytable
SET
  a = CASE 
    WHEN [condition1]
      THEN coalesce((@b:= [b expression]) +null, (@c:= [c expression]) +null, [a expression])
    WHEN [condition2]
      THEN coalesce((@b:= [b expression]) +null, (@c:= [c expression]) +null, [a expression])
    ELSE coalesce((@b:= [b expression]) +null, (@c:= [c expression]) +null, [a expression])
    END,
  b = @b,
  c = @c

Append to each input parameter of coalesce '+null' to ensure that the expression is executed and result stored in a variable but the result of the expression is not returned by the coalesce function.

Tested on MySQL.

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32230

Is it possible to update both columns using a single case statement ?

No. A CASE expression has a single atomic value. A single CASE expression cannot have a value of two fields. You cannot set both request_status_id and is_changed from a single use of CASE ... END.

It's possible to update both fields in the same UPDATE statement, but not the same CASE expression.

However, your expression is not problematic, and your assignments here aren't going to step on each other. The data as it's read is locked when the statement begins executing. It uses the table as it existed prior to the statement running.

Consider:

CREATE TABLE TestTable (
    Odd TINYINT NOT NULL,
    Even TINYINT NOT NULL,
    PRIMARY KEY (Odd, Even)
);

INSERT INTO TestTable (Odd, Even) VALUES (1, 2);
INSERT INTO TestTable (Odd, Even) VALUES (3, 4);
INSERT INTO TestTable (Odd, Even) VALUES (5, 6);
INSERT INTO TestTable (Odd, Even) VALUES (7, 8);

SELECT * FROM TestTable;

UPDATE TestTable SET Odd = Even, Even = Odd;

SELECT * FROM TestTable;

SQLFiddle

Unless you're doing something weird like recursive CTEs, data are pretty much set as they were prior to statement execution.

Upvotes: 2

Milan Švec
Milan Švec

Reputation: 1813

Or if the logic is so simple, you can do it using 2 updates, the second update is based on the result of first update. But again, you need to have reason to do it this way.

UPDATE PAYMENT_HISTORY
  SET request_status_id =
   CASE
     WHEN PHP.request_status_id = PHP.prevRequest_status_id THEN NULL 
     ELSE PHP.request_status_id
   END

UPDATE PAYMENT_HISTORY SET 
  is_changed = 
    CASE
      WHEN request_status_id is NULL THEN 0
      ELSE 1
    END

OR with more effective execution (the second update will update only lines that need to be updated)

UPDATE PAYMENT_HISTORY
  SET 
   request_status_id =
     CASE
       WHEN PHP.request_status_id = PHP.prevRequest_status_id THEN NULL 
       ELSE PHP.request_status_id
     END,
   is_changed=0


UPDATE PAYMENT_HISTORY SET 
    is_changed = 1
  WHERE 
    request_status_id is not NULL

Upvotes: 1

Roman Tumaykin
Roman Tumaykin

Reputation: 1931

Please see the BOL for update clause: https://msdn.microsoft.com/en-us/library/ms177523.aspx

Specifically column_name = { expression | DEFAULT | NULL }

Expressions are allowed only on the right side, so you can't write an expression to choose which column to update.

Upvotes: 2

Milan Švec
Milan Švec

Reputation: 1813

Basically it's not possible. You can move the CASE..END to function and call it this way, but the impact is discutable...

UPDATE PAYMENT_HISTORY
SET 
    request_status_id = dbo.fnCalc(PHP.request_status_id, PHP.prevRequest_status_id, 1),
    is_changed = dbo.fnCalc(PHP.request_status_id, PHP.prevRequest_status_id, 2)

Upvotes: 1

Related Questions