Is update behavior undefined in this condition? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Is update behavior undefined in this condition?

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2009-02-26 : 15:28:30

Does anyone know if the update listed below is undefined? It seems like the final result could either be NULL or 'octopus', though I always seem to get 'octopus'. It seems consistent though this may because I'm just using small simple tables. Here is the query:


DECLARE @Table1 Table(ID INT, Value varchar(20))
DECLARE @Table2 TABLE(ID INT, Value varchar(20))

INSERT INTO @Table1 (ID, Value) VALUES (1, 'foo')
INSERT INTO @Table2 (ID, Value) VALUES (1, NULL)
INSERT INTO @Table2 (ID, Value) VALUES (1, 'octopus')
INSERT INTO @Table2 (ID, Value) VALUES (1, NULL)

UPDATE	a
SET		a.Value = b.Value
FROM	@Table1 a 
		JOIN @Table2 b ON a.ID = b.ID

-- @Table1.Value always seems to be 'octopus', which is the first non-null value in @Table2
SELECT * FROM @Table1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-26 : 19:59:50

According to MSDN http://msdn.microsoft.com/en-us/library/ms177523.aspx the "results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic."

They don't specifically address the case where there is only one non-null value available, but I would think that the same rule applies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources