×

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

@Florida

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)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论