What can cause 'rows affected' to be incorrect?

Question

Using Microsoft SQL Server Management Studio 2008. I have done a simple transaction:

BEGIN TRAN

SELECT ko.ID, os.ID AS ID2
FROM table_a AS ko
JOIN table_b AS os ON os.ID=ko.ID
WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL);

UPDATE table_b SET the_date=ko.the_date
FROM table_a AS ko
JOIN table_b AS os ON os.ID=ko.ID
WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL);

SELECT ko.ID, os.ID AS ID2
FROM table_a AS ko
JOIN table_b AS os ON os.ID=ko.ID
WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL);


ROLLBACK

So the SELECT and UPDATE should be the same. And the result should return 0 rows. But the UPDATE affects one row less than the SELECT gets from DB:

(61 row(s) affected)

(60 row(s) affected)

(0 row(s) affected)

What am I missing here?

Answer 1

I'd suspect the most likely reason is that Table_a in your example has a row with a duplicate ID in it - this cases an additional row to appear in the join in your first select, but the update only deals with rows in Table_b, so your duplicate row doesn't matter. This statement should give you the culprit:

SELECT ko.ID
FROM table_a AS ko
JOIN table_b AS os ON os.ID=ko.ID
WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL)
GROUP BY ko.ID
HAVING COUNT(*) > 1