Update + From: more updates than expected

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)

Update + From: more updates than expected

Author

Topic

abybees
Starting Member

3 Posts

Posted - 2009-07-01 : 13:47:15

I am using the following SQL statement:
UPDATE Table SET Assigned = 0 FROM (SELECT TOP 1 * FROM Table WHERE Assigned IS NULL ORDER BY DateCreated ASC) AS T

Essentially:
Set the run state to zero, for the oldest row which has "Assigned" set to NULL.

This should update only ONE row, since there is a TOP 1 restriction. Yet, in some cases, multiple rows were updated.

Any idea what causes this behavior?

Thanks for your help,
Andy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:52:17

why use derived table? you can simply use


UPDATE TOP (1) TABLE
SET Assigned = 0 
WHERE Assigned IS NULL
ORDER BY DateCreated ASC

abybees
Starting Member

3 Posts

Posted - 2009-07-01 : 15:37:54

Thanks, that solves my problem (apparently TOP for UPDATE is a SQL 2005 extension which I use). I still do not understand the behavior I observed, however.

abybees
Starting Member

3 Posts

Posted - 2009-07-01 : 18:09:21

Actually I take it back. Unfortunately you suggestion, which was as follows, does NOT work:
UPDATE TOP (1) TABLE
SET Assigned = 0
WHERE Assigned IS NULL
ORDER BY DateCreated ASC

Indeed, one gets "incorrect syntax near the keyword 'order'". This, because UPDATE + ORDER queries are considered illegal (in short because SQL server feels it should itself be able to control the insertion order, and potentially parallelize insertions). It seems the same principle applies in SQL server to UPDATE + ORDER.

So, going back to my original question, why were multiple rows affected in spite of the TOP 1 clause?
UPDATE Table SET Assigned = 0 FROM (SELECT TOP 1 * FROM Table WHERE Assigned IS NULL ORDER BY DateCreated ASC) AS T

I can certainly use an IN subquery (which works), but the above query appears to be totally misbehaving.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:42:29

what does this return you?

SELECT TOP 1 * FROM Table WHERE Assigned IS NULL ORDER BY DateCreated ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources