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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With