SQL Update with Join and Sub Query

January 18, 2023

I have the following queries and have trouble putting them together:

DECLARE @Value1 INT = 3
DECLARE @Value2 INT = 6

UPDATE TableA SET 
        Column1 = B.NewValue,
FROM TableA A   INNER JOIN TableB B ON A.NumberId = B.NumberId AND 
AND A.Type = @Value1

UPDATE TableA SET 
        Column2 = B.NewValue,
FROM TableA A INNER JOIN TableB B ON A.NumberId = B.NumberId AND 
AND A.Type = @Value2

My goal is to have one query with a join that updates the columns depending on the values in the join.
This I just an example (in my case there are more columns and therefore more queries) but overall I want to have as few queries as possible (in this example: one query instead of two)

DECLARE @Value1 INT = 3
DECLARE @Value2 INT = 6

UPDATE TableA SET 
        Column1 = B.NewValue,  --if the join joins on @Value1
        Column2 = B.NewValue,  --if the join joins on @Value2
FROM TableA A   INNER JOIN TableB B ON A.NumberId = B.NumberId AND 
AND A.Type = B.@Value1/@Value2

Is this possible (using a sub query for example)?

>Solution :

You can try using CASE EXPRESSION

UPDATE TableA SET 
        Column1 = CASE WHEN A.Type = @Value1 THEN B.NewValue 
                       ELSE A.Column1 END, 
        Column2 = CASE WHEN A.Type = @Value2 THEN B.NewValue 
                       ELSE A.Column2 END 
FROM TableA A INNER JOIN TableB B ON A.NumberId = B.NumberId AND 
AND A.Type IN (@Value1, @Value2)