A question on SQL Update - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A question on SQL Update

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2005-07-28 : 11:49:30

This is something that has always bothered me.

Consider this query:

UPDATE T_TABLE
SET Name = n.Name
FROM T_TABLE t
join T_Names n on t.ID = n.ID

If the join is one (T_Table) to many (T_Names) which record does the actual update? It seems like I can run the same query many times and get different answers, which has happened to me sometimes.

Dom

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-28 : 13:22:42

The first question I would ask is why you have a NAME column in both of these tables if you are joining on the ID values. Seems redundant.

Are you saying that you have tables like this:


T_Table:
--------

ID  |  Name
-----------
1   |  tom
2   |  dick
3   |  harry


T_Names:
-------

ID  |  Name
-----------
1   |  foo
1   |  bar
1   |  foobar
1   |  foo_bar
etc...

-ec

DOlivastro
Starting Member

41 Posts

Posted - 2005-07-28 : 14:01:21

Essentially, yes. I just made up the example. The question is: If you have a one-to-many join, which record does the update?

Dom

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 14:06:13

joined ones...
without an example there's little room for much debate

Go with the flow & have fun! Else fight the flow

b74smith
Starting Member

3 Posts

Posted - 2005-07-30 : 10:56:25

The update is non deterministic - it appears to use one of the joined in rows, but you don't know which one. In other words you should ensure you specify a 1-1 join, if you want to update in a consistent meaningful way.

This is discussed in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp
under remarks with an example.

DOlivastro
Starting Member

41 Posts

Posted - 2005-07-30 : 23:23:18

Thanks to b74smith for the final answer.

Dom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources