This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

SQL server 2000, Update from another table

marsher(LP3A)

Need to update a table from another table which is in one-many relationship. Example as follows:
Table1:
t1_ID t1_Field1
1 null

Table2:
t2_ID t2_Filed1 t1_ID
1 abc 1
2 def 1

t1_ID and t2_ID are primary key with identity attribute respectively. I used an Update statement to update Table1’s t1_Field1:

Update Table1 set t1_Field1=b.t2_Field1 from Table1 a, Table2 b where a.t1_ID=b.t1_ID.

In one environment, it got result as t1_Field1=abc, but in another environment, it is t1_Field1=def. Both environments are SQL Server 2000. I cannot explain why one of them gets the first value from Table2 but the other gets the last value from Table2.

Could anyone help on this? I’ve compared most of the settings for these two servers & databases but cannot find any difference between them. Maybe I am missing some different settings.

(#4368619@0)
Last Updated: 2008-4-1
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / SQL server 2000, Update from another table -marsher(LP3A); 2008-4-1 {852} (#4368619@0)
Need to update a table from another table which is in one-many relationship. Example as follows:
Table1:
t1_ID t1_Field1
1 null

Table2:
t2_ID t2_Filed1 t1_ID
1 abc 1
2 def 1

t1_ID and t2_ID are primary key with identity attribute respectively. I used an Update statement to update Table1’s t1_Field1:

Update Table1 set t1_Field1=b.t2_Field1 from Table1 a, Table2 b where a.t1_ID=b.t1_ID.

In one environment, it got result as t1_Field1=abc, but in another environment, it is t1_Field1=def. Both environments are SQL Server 2000. I cannot explain why one of them gets the first value from Table2 but the other gets the last value from Table2.

Could anyone help on this? I’ve compared most of the settings for these two servers & databases but cannot find any difference between them. Maybe I am missing some different settings.

哪个是你想要的结果？你这个update语句有问题， where clause return multi results，而且你没有sorting，所以最后得到哪个结果是要看你两个环境下的index setting等等。关键fix your update statement. -luoboyang(萝卜秧); 2008-4-1 (#4368660@0)
Two solutions: -deep_blue(BLUE); 2008-4-1 {247} (#4368824@0)
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.
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)

@Las Vegas

SQL server 2000, Update from another table

Replies, comments and Discussions:

More Topics