Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marsherfu
Starting Member
4 Posts |
 Posted - 2008-04-01 : 13:42:13
|
| Need to update a table from another table which is in one-many relationship. Example as follows:Table1: t1_ID t1_Field11 nullTable2: t2_ID t2_Filed1 t1_ID1 abc 12 def 1t1_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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-01 : 13:54:41
|
| You cant guarantee what order values are returned by SQL Server for updation. Thats why the value varies. If you specifically want to update with particular value like first,lst etc use aggregate functions like MIN(Value),MAX(Value) ...after grouping. |
 |
|
|
marsherfu
Starting Member
4 Posts |
Posted - 2008-04-01 : 14:07:28
|
| Thank you for your prompt reply. The thing is I did more tests and found in one environment it always pick the first value but in the other it always pick the last value. I am wondering if it has anything to do with the system settings difference. How can I easily compare the system settings for SQL Servers? or what T-SQL statements I can use to display the system settings, such as sp_configure? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-01 : 14:33:30
|
| Do you have any additional indexes on one environment? |
|
|
|
marsherfu
Starting Member
4 Posts |
Posted - 2008-04-01 : 14:38:34
|
| No. I've a tool to compare the two DB and the tables are identical. no additional index. |
|
|
|
|
|
|
Update from a link table