incorrect update query output - 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

SQL Server Development (2000)

incorrect update query output

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 00:17:10

I have implemented cascade update via trigger in SQL Server 7.0 which updates the primary key value in primary table successfully along with updates in secondary tables. I've written a loop to update all the values of primary key as below:
-----------------
declare @counter int
declare @rowcounter int

declare @query nvarchar(1000)

set @counter = (select count(*) from [dbo].[Order])
set @rowcounter = 0
while(@counter>0)
begin
set @query='update [dbo].[Order] set OrderID = '+ cast( (@rowcounter + 1) as varchar)+' where OrderID =
(select top 1 OrderID from [dbo].[Order] where OrderID not in (Select top ' + cast(@rowcounter as nvarchar)+ ' OrderID from [dbo].[Order]))'
print @query
exec sp_executesql @query
set @counter = @counter - 1
set @rowcounter = @rowcounter + 1
end
-----------------
update trigger on [dbo].[Order] table also updates the other two tables for OrderID. Now manually for each value of OrderID in [dbo].[Order] are successful. But with the SQL code written above, it leaves one row as it is.

I have 3 rows with OrderID 10001,10002 and 10003 in order. When I run the above query, it updates OrderID to 1,3 and 10003. It does not change 10003. I don't understand where the query is wrong.

Thanks in advance.

Ujjaval.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-20 : 00:43:49

Hi ujjaval,

try this i believe orderid column is numeric
declare @counter int
declare @query nvarchar(1000)

set @counter = (select count(*) from [dbo].[Order])
while(@counter>0)
begin
set @query='update [dbo].[Order] set OrderID = '+ cast( (@counter) as varchar)+' where OrderID =
(select top 1 OrderID from [dbo].[Order] order by orderid desc)'
print @query
exec sp_executesql @query
set @counter = @counter - 1
end
-----------------

ujjaval
Posting Yak Master

108 Posts

Posted - 2005-12-20 : 01:02:05

No, actually OrderID is varchar here. So, I don't think we need to use 'order by'. Anyother idea?

thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-20 : 01:07:27

quote:
I don't think we need to use 'order by'

Yes you need to use Order By

rrb has explain this here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59359[/url]

set @query = 'update [dbo].[Order] set OrderID = ' + 
    cast( (@rowcounter + 1) as varchar)+ 
    ' where OrderID = (select top 1 OrderID from [dbo].[Order] where OrderID not in (Select top ' + cast(@rowcounter as nvarchar)+ ' OrderID from [dbo].[Order] order by OrderID))'

-----------------
[KH]
Learn something new everyday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources