Update Query that Lists Rows Not Updated

user2274742 0
Viewed 29

I have a 12 million row SQL Server table that when I run the following query it shows approximately 11.6 million rows updated:

UPDATE [HCRIS]
SET [HCRIS].[ST_ABB] = dbo.[PRVDR_CHANGE].[state_abbreviation]
FROM [HCRIS]
INNER JOIN dbo.[PRVDR_CHANGE]
ON [HCRIS].[PRVDR_NUM] = dbo.[PRVDR_CHANGE].[PRVDR_NUM]
WHERE [HCRIS].[PRVDR_NUM] = dbo.[PRVDR_CHANGE].[PRVDR_NUM];

I have checked for nulls and non-numeric values in a numeric column but would love to pinpoint those rows that were not updated. My intent is to update all rows.

Thank you.

sql-server sql
1 Answers

Use EXCEPT to find rows that won't get updated - where you select all rows in the first query and rows you would have updated in the second.

SELECT H.*
FROM [HCRIS] H

EXCEPT

SELECT H.*
FROM dbo.[HCRIS] H
INNER JOIN dbo.[PRVDR_CHANGE] C
ON H.[PRVDR_NUM] = C.[PRVDR_NUM]
WHERE H.[PRVDR_NUM] = C.[PRVDR_NUM];
Dale K0
Related