Styjun

I created a query to update a flag, I used a CASE statement to determine the value. However when I run the query as an UPDATE statement only about half of the expected rows are updated? More interestingly I have run the exact same UPDATE query previously on the same data and it worked as expected (looking at the old vs new is what caused me to investigate).

I tried SELECT query using the same CASE statement, I get the correct results, but switching it back to UPDATE only updates roughly half the records.

Moving the criteria to the WHERE clause fixed the issue. It seems to be the CASE statement in the SET part that causes problems. I can't figure out why? I'd like to know so that avoid whatever mistake I've made in the future.

Original Code:

UPDATE D
SET PUBLISH_FLAG =
CASE WHEN
 MAPPED_CAT NOT IN(1,2,3)
 AND SRC != '999'
 AND RECEIVED_DATE is not null
 AND RECEIVED_DATE <= D.CENSUS_DATE
 AND SCHEDULED_FLAG = 'N'
 THEN 'Y'
 ELSE 'N'
END
FROM TBL_DATA D
INNER JOIN TBL_PUBLISH V
 ON D.ID = V.ID
 AND D.CENSUS_DATE = V.CENSUS_DATE
 AND D.VERSION_NUMBER = V.VERSION_NUMBER
LEFT JOIN TBL_CAT_MAP C
 ON D.SRC_CATEGORY = C.SOURCE_CAT 

Working code:

UPDATE D
SET PUBLISH_FLAG = 'Y'
FROM TBL_DATA D
INNER JOIN TBL_PUBLISH V
 ON D.ID = V.ID
 AND D.CENSUS_DATE = V.CENSUS_DATE
 AND D.VERSION_NUMBER = V.VERSION_NUMBER
LEFT JOIN TBL_CAT_MAP C
 ON D.SRC_CATEGORY = C.SOURCE_CAT 
WHERE
 MAPPED_CAT NOT IN(1,2,3)
 AND SRC != '999'
 AND RECEIVED_DATE is not null
 AND RECEIVED_DATE <= D.CENSUS_DATE
 AND SCHEDULED_FLAG = 'N'

I thought that both should produce exactly the same results? What am I missing?

To help clarify the below code has 2 shows the difference, the 'PUBLISH_FLAG' column (updated using either my original code or PSK's answer) has 10162 'Y' values (the rest 'N'), the pub_2 column has the correct 18917 'Y' values.

SELECT
PUBLISH_FLAG,
CASE WHEN
 MAPPED_CAT NOT IN(1,2,3)
 AND SRC != '999'
 AND RECEIVED_DATE is not null
 AND RECEIVED_DATE <= D.CENSUS_DATE
 AND SCHEDULED_FLAG = 'N'
 THEN 'Y'
 ELSE 'N'
END as pub_2
FROM TBL_DATA D
INNER JOIN TBL_PUBLISH V
 ON D.ID = V.ID
 AND D.CENSUS_DATE = V.CENSUS_DATE
 AND D.VERSION_NUMBER = V.VERSION_NUMBER
LEFT JOIN TBL_CAT_MAP C
 ON D.SRC_CATEGORY = C.SOURCE_CAT 
WHERE
CASE WHEN
 MAPPED_CAT NOT IN(1,2,3)
 AND SRC != '999'
 AND RECEIVED_DATE is not null
 AND RECEIVED_DATE <= D.CENSUS_DATE
 AND SCHEDULED_FLAG = 'N'
 THEN 'Y'
 ELSE 'N'
END = 'Y'

Your first query is definitely not the same as your second. In fact from what I see here I would state your update with the CASE is the correct one since it is updating both sides of the flag. The other query with the WHERE does not update the flag to N where it is supposed to. How exactly are you determining the "correct" number of updates to be expected? I think you're expecting your UPDATE statement to have as many rows updated as the SELECT statement, while this is not always the case. The JOIN you are making could produce a cartesian product depending on your filters.

Consider the query below.

CREATE TABLE #table1 (Field_1 INT, Field_2 VARCHAR(MAX))
INSERT INTO
 #table1
VALUES
 (1, 'Item A'),
 (2, 'Item B'),
 (3, 'Item C'),
 (4, 'Item D'),
 (5, 'Item E')

CREATE TABLE #table2 (Field_1 INT, Field_2 VARCHAR(MAX))
INSERT INTO
 #table2
VALUES
 (1, 'Item A'),
 (1, 'Item B'),
 (2, 'Item B'),
 (2, 'Item C'),
 (3, NULL)

-- This produces 7 rows:
SELECT
 *
FROM
 #table1
LEFT JOIN
 #table2 ON #table1.[Field_1] = #table2.[Field_1]

-- This updates 1 row. This is akin to your second query. Only one flag value is changed.
-- You would still have to write an UPDATE statement for the 'N' flag update.
UPDATE
 #table1
SET
 #table1.[Field_2] = 'Y'
FROM
 #table1
LEFT JOIN
 #table2 ON #table1.[Field_1] = #table2.[Field_1]
WHERE
 #table2.[Field_2] = 'Item C'

-- Because your UPDATE statement only updates the values to 'Y' where a condition matches, only one record is changed here.
-- The others are left untouched.
SELECT
 *
FROM
 #table1

-- Now what happens if we perform the reverse UPDATE.
UPDATE
 #table1
SET
 #table1.[Field_2] = 'N'
FROM
 #table1
LEFT JOIN
 #table2 ON #table1.[Field_1] = #table2.[Field_1]
WHERE
 NOT (#table2.[Field_2] = 'Item C')

-- First of all we notice that we are not dealing with NULL values at all so only two records get changed to 'N'.
-- The first record gets changed because it does not have a match on 'Item C'.
-- The second record also gets changed because it does not have a match on 'Item C', i.e. there is at least one record without an 'Item C' match.
-- The last three records have either no match in the JOIN or are NULL in #table2. Meaning they are not updated.
-- This is why I'm more a fan of your CASE query, because in theory it should deal with setting everything to the correct value.
SELECT
 *
FROM
 #table1

-- Let's see what would happen with a CASE statement.
-- Since our JOIN is a cartesian product there are multiple options for #table1.Id == 2: it can be updated to both N and Y.
-- N is chosen by T-SQL. You will see that after the UPDATE.
SELECT
 *, CASE WHEN #table2.[Field_2] = 'Item C' THEN 'Y' ELSE 'N' END
FROm
 #table1
LEFT JOIN
 #table2 ON #table1.[Field_1] = #table2.[Field_1]

-- This updates 5 rows, maybe you would have expected 7 here based on the above SELECT statement?
-- You can also notice how it updates everything to N, that's because our CASE deals with both sides.
-- It's either 'Y' or either 'N'. It will always touch every record it can to UPDATE it.
-- This in contrast with an UPDATE statement which will only touch one side and because of JOIN clauses and NULL values
-- it's entirely possible that both UPDATE statements do not touch the entire table if written incorrectly.

-- You would have to write an UPDATE statement like this one, which comes after the first.
--UPDATE
-- #table1
--SET
-- #table1.[Field_2] = 'N'
--FROM
-- #table1
--LEFT JOIN
-- #table2 ON #table1.[Field_1] = #table2.[Field_1]
--WHERE
-- #table1.[Field_2] <> 'Y' OR #table1.[Field_2] IS NULL

-- In conclusion this means that if you want to be absolutely sure you have updated all values to their correct setting: use CASE.
-- But if you only care about setting 'Y' to the correct value: don't use CASE.
-- If you do use CASE, make sure you are definitely performing your JOIN correct and you are calculating the correct value for both sides.
UPDATE
 #table1
SET
 #table1.[Field_2] = CASE WHEN #table2.[Field_2] = 'Item C' THEN 'Y' ELSE 'N' END
FROM
 #table1
LEFT JOIN
 #table2 ON #table1.[Field_1] = #table2.[Field_1]

SELECT
 *
FROM
 #table1

DROP TABLE #table1
DROP TABLE #table2

Both the queries are not the same, in the first query all the unmatched records in the switch case will be set as 'N', irrespective of their current state.

The second query is the right approach for this type of scenario where you are updating only the required records.

You can change your first query like following to avoid changing the unmatched records.

UPDATE D
SET PUBLISH_FLAG =
CASE WHEN
 MAPPED_CAT NOT IN(1,2,3)
 AND SRC != '999'
 AND RECEIVED_DATE is not null
 AND RECEIVED_DATE <= D.CENSUS_DATE
 AND SCHEDULED_FLAG = 'N'
 THEN 'Y'
 ELSE PUBLISH_FLAG --Modified
END
FROM TBL_DATA D
INNER JOIN TBL_PUBLISH V
 ON D.ID = V.ID
 AND D.CENSUS_DATE = V.CENSUS_DATE
 AND D.VERSION_NUMBER = V.VERSION_NUMBER
LEFT JOIN TBL_CAT_MAP C
 ON D.SRC_CATEGORY = C.SOURCE_CAT 

Note: In above query, I changed the else part.

it depends on you flag conditions, which you have made.

as per your left join it will make a join to number of records but when you apply Where conditions then the records will filter out , but you apply case then number of records remain same as earlier but as per case condition only matched flag will update not all. So it is updating on half of records.