Why SELECT and UPDATE Return Different Record Counts/Affected

Question

I wrote a SELECT query to find out how many records will be impacted with my UPDATE query.

The SELECT and UPDATE returned different record counts.

Here is my SELECT query:

SELECT *
FROM T1
JOIN T2 on T1.ID = T2.ID
WHERE T1.Name IS NULL
  AND T2.Status = 'happy'

Here is my UPDATE query:

UPDATE T1
SET T1.Name = T2.Name
FROM T1
JOIN T2 on T1.ID = T2.ID
WHERE T1.Name IS NULL
  AND T2.Status = 'happy'

My SELECT returns 19K records, and my UPDATE affects 12K records. Please note that the WHERE clause is exactly the same for both the SELECT and UPDATE.

What is causing the discrepancy in records counts between the SELECT and UPDATE queries?

Can you please help me understand what is happening here?

Thanks in advance!!

Answer 1

That can happen on a one to many join when you're updating the one side. In your case, it looks like there are more than one T2 row for some of your T1 rows, and the server will return the T1 row as many times as there is matches in the T2 table.

Check if this matches your update count:

SELECT count(distinct T1.ID)
FROM T1
JOIN T2 on T1.ID = T2.ID
WHERE T1.Name IS NULL
  AND T2.Status = 'happy'