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

UPDATE...FROM does not detect ambiguities like this one:

CREATE TABLE dbo.source
    (
      id INT NOT NULL ,
      SomeNumber INT
    )
GO
CREATE TABLE dbo.target
    (
      id INT NOT NULL
             PRIMARY KEY ,
      SomeNumber INT
    )
GO
INSERT  INTO dbo.source
        ( id, SomeNumber )
        SELECT  1 ,
                2
        UNION ALL
        SELECT  1 ,
                3

INSERT  INTO dbo.target
        ( id, SomeNumber )
        SELECT  1 ,
                0

UPDATE  dbo.TARGET
SET     SomeNumber = s.SomeNumber
FROM    dbo.source AS s
        JOIN dbo.TARGET AS t ON s.id = t.id

The row in your target table has two matches in the source, and we cannot know in advance which value will eventually update the target.

Answer 2

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