CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Creztian
Creztian

Reputation: 48

Updating multiple values SQL, checking on values in update statement

I am going through some unwanted behaviour in my code, and wonder if the order of which values are updated is important.

We have value x,y,z that are evaluated based on each other. X is either 'I', 'U' or 'D' Y is either 'J' or 'N' Z is either 'J' or 'N'

which values that are valid is evaluated based on the other ones such as:

UPDATE values
SET X = CASE WHEN X IN ('I','U') AND (Y = 'N' OR Y IS NULL) THEN 'D'
             WHEN X = 'D' AND Y = 'J' THEN 'N'
ELSE X
END,
Y = CASE WHEN X IN ('I','U') AND (Y = 'N' OR Y IS NULL) THEN 'J'
         WHEN X = 'D' AND Y = 'J' THEN 'N'
ELSE Y
END,
Z = CASE WHEN X <> 'D' AND Z = 'J' THEN 'N'
ELSE Z
END

My question is, does the order of the cases affect the updates after, or not. Would the updated X value be evaluated in the following cases?

Upvotes: 1

Views: 87

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82504

Please note that the order of the columns in the SET clause of an update is meaningless. The entire row is updated at once in an update statement, meaning you are validating your updates using the old values in the columns.

Here's a quick demo:

Create and populate sample table:

DECLARE @T AS TABLE
(
    A int,
    B int
)

INSERT INTO @T (A, B) VALUES (1, 2)

Update B to the value of A, and A to 3:

UPDATE @T 
SET B = A,
    A = 3

SELECT A, B 
FROM @T

-- Result:

A   B
3   1

Update A to 4, and B to the value of A:

UPDATE @T 
SET A = 4,
    B = A

SELECT A, B 
FROM @T

-- Result:    

A   B
4   3

See it live on rextester.

What you can do is use a stored procedure or a prepared statement, and set the values on the table according to the parameters values:

CREATE PROCEDURE UpdateValues
(
    @X char(1), 
    @Y char(1), 
    @Z char(1)
)
AS

UPDATE [values]
SET X = CASE WHEN @X IN ('I','U') AND (@Y = 'N' OR @Y IS NULL) THEN 'D'
             WHEN @X = 'D' AND @Y = 'J' THEN 'N'
ELSE @X
END,
Y = CASE WHEN @X IN ('I','U') AND (@Y = 'N' OR @Y IS NULL) THEN 'J'
         WHEN @X = 'D' AND @Y = 'J' THEN 'N'
ELSE @Y
END,
Z = CASE WHEN @X <> 'D' AND @Z = 'J' THEN 'N'
ELSE @Z
END

This way, you will be setting the values on the table based on the new values and not on the old values.

Upvotes: 1

Related Questions