This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / SQL server 2000, Update from another table
-marsher(LP3A);
2008-4-1
{852}
(#4368619@0)
-
哪个是你想要的结果？你这个update语句有问题， where clause return multi results，而且你没有sorting，所以最后得到哪个结果是要看你两个环境下的index setting等等。关键fix your update statement.
-luoboyang(萝卜秧);
2008-4-1
(#4368660@0)
-
Two solutions:1. Apply an additional condition to limit records in one to one from both tables (better use this one).
2. Using sub-query and ORDER BY clause to enforce specific sorting for table2 then join table1 and the sub-query for update statement.
-deep_blue(BLUE);
2008-4-1
{247}
(#4368824@0)
-
Thanks for 萝卜秧&BLUE's input. Not seek for solution because I might have to change the DB schema but an explanation for why. Just migrated an App from server 1 to server 2, same input from the users, but diff result. I am asked for why. Any clue?
-marsher(LP3A);
2008-4-1
(#4368852@0)
-
When you use multi-records update one record, system will use the first record. And which record is the first one will depend one table indexing unless you specify sorting.
-deep_blue(BLUE);
2008-4-1
(#4368899@0)
-
That's also my understanding. But since settings look like same, server 2 should work same as server 1. I don't have extra indexing on either table. Does SQL server pick the record randomly or always the first one? I'm puzzled.
-marsher(LP3A);
2008-4-1
(#4369043@0)
-
Therefore the best way is to limit update statement to one to one rather than many to one.
-deep_blue(BLUE);
2008-4-2
(#4370173@0)
-
It's never guaranteed. Maybe you should use INSERT/SELECT instead of UPDATE.
-canadiantire(轮胎 - favete lingui);
2008-4-2
(#4370199@0)
-
I forgot one thing. The default sorting is timestaple of data entering.
-deep_blue(BLUE);
2008-4-2
(#4370217@0)
-
this statement is useless in reality, you're taking the risk of uncertainty.
it's very important you always use a one-to-one update.
-flood(flood);
2008-4-3
(#4373809@0)