使用Microsoft sql Server Management Studio 2008.我做了一个简单的事务：
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
所以SELECT和UPDATE应该是一样的.结果应该返回0行.但UPDATE影响的一行少于SELECT从DB获取的行：
(61行受影响)
(60排受影响)
(0行(s)受影响)
我在这里想念的是什么？
解决方法
我怀疑最可能的原因是你的例子中的Table_a中有一行有重复的ID – 这种情况下你的第一个选择中的连接中会出现另一行,但更新只处理Table_b中的行,所以你的重复行无关紧要.这句话应该给你罪魁祸首：
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
