Help with Update Query - 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 2005 Forums

Transact-SQL (2005)

Help with Update Query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-01-21 : 13:52:24

I am trying to update a column callled matchcode with the rownumber for earch record in the CosymList2010 table.
When I run the below query I get the following:

"Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."

begin transaction
update CosymList2010
set matchcode = (select ROW_NUMBER() OVER (ORDER BY sym) from CosymList2010)
from CosymList2010

Can someone help me out with my syntax?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-21 : 14:36:13

Here is one way - ASSUMING that sym is unique in the table.


update s set 
       s.matchcode = d.matchCode
from   CoSymList2010 s
inner join (
       select sym
              ,row_number() over (order by sym) as matchCode 
       from   CoSymList2010
       ) d
              on d.sym = s.sym

Be One with the Optimizer
TG

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-01-22 : 08:03:48

Thanks for the suggestion, I ended up using a counter.

begin transaction
DECLARE @counter int
SET @counter = 0
UPDATE CosymList2010
SET @counter = matchcode = @counter + 1

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 08:12:14

@gamn: did you need an Order By for that? (what I think you have got is a unique value for [matchcode], but no particular sequence)

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-01-22 : 08:24:56

When executed, the update statement appears to have updated each record in order with a sequential numeric value for match code. This is what I needed so it worked for me.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 10:58:50

OK, beware that the order will work most times (I imagine its the Clustered Index) but this is not guaranteed, and once-in-a-while it won't happen in order.

having said that I don't think you can put an ORDER BY on an UPDATE, so if you did need them to be in order, and it didn;t work, you'd need Plan B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources