CODEWITHSUNDEEP
sql-server
navigator
navigator

Reputation: 1708

Update column based on another column's value which is also being updated

I wish to update column A with null. And I also wish to update column B with null, but only if it's value matched the original value of column A.

Update MyTable
Set A=Null,
    B=Case When B=A Then Null Else B End

Will the above statement work? Is the value of A within the Case statement already Null when evaluated? And does the order of the updates matter?

Any way to write this better and avoid setting B=B when it's not really required? I need to do this within 1 statement as the actual query is long and complex involving many joins and sub-queries.

Upvotes: 2

Views: 1133

Answers (2)

Efran Cobisi
Efran Cobisi

Reputation: 6484

The order of the columns being updated does not matter: in fact, you will also get the original value of A (that is, the one you would get from the deleted virtual table if you were writing a trigger), despite of the value you are assigning to it during the update.

You can easily test this with this script, which contains two UPDATEs with a different order of the updated columns:

CREATE TABLE Test
(
    Id INT PRIMARY KEY NOT NULL,
    X INT,
    Y INT
)

INSERT Test (Id, X, Y)
VALUES (1, 2, 3)
INSERT Test (Id, X, Y)
VALUES (4, 5, 6)

UPDATE Test
SET X = X + 1,
    Y = X
WHERE Id = 1

UPDATE Test
SET Y = X,
    X = X + 1
WHERE Id = 4

SELECT *
FROM Test

Here the SELECT would return this:

Id  X   Y
1   3   2
4   6   5

As you can see, the updated value of Y is always the same of the original X, despite the different order of the UPDATEs.

Upvotes: 1

sagi
sagi

Reputation: 40491

You query should be fine if you just place B before A like this becuase the order does matter. It is possible that it will also work like you wrote it, but I doubt it.

UPDATE MyTable
SET B = CASE WHEN A = B then null else B END,
    A = NULL

Upvotes: 1

Related Questions