If you are updating multiple rows in one statement, but only want to update a value in a column if a condition is satisfied, then you need to perform some sort of if logic inside your update. Here’s a straight forward way of doing this.
In the example below, I am just going to use 2 temp table variables. I’ll update the @SourceTable with the data from the @UpdateTable… but only where the UpdateFlag field is set to 1.
DECLARE @SourceTable TABLE
(AnimalID INT,
AnimalName VARCHAR(255))
DECLARE @UpdateTable TABLE
(AnimalID INT,
UpdateToValue VARCHAR(255),
UpdateFlag BIT)
INSERT
INTO @SourceTable
VALUES (1, 'Dog'),
(2, 'Cat'),
(3, 'Snake')
INSERT
INTO @UpdateTable
VALUES (1, 'Pig', 1),
(2, 'Cow', 0),
(3, 'Turkey', 1)
UPDATE S
SET S.AnimalName = CASE WHEN U.UpdateFlag = 1 THEN U.UpdateToValue
ELSE S.AnimalName
END
FROM @SourceTable S
INNER JOIN @UpdateTable U ON U.AnimalID = S.AnimalID
SELECT *
FROM @SourceTable S
In the example above, you can see that the AnimalName is only updated when the UpdateFlag column is set to 1. The 2nd record “Cat” is not updated with the value “Cow” because it has a 0 for the UpdateFlag. This is done using a case statement on the SET operation. The example above is very simple. This case logic in the SET operation becomes much more helpful in more complex queries.