Reputation: 789
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
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
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;
Unless you're doing something weird like recursive CTEs, data are pretty much set as they were prior to statement execution.
Upvotes: 2
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
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
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