using ORDER BY in UPDATE statement

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)

using ORDER BY in UPDATE statement

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-07 : 12:59:04

I have the following query:


declare @counter int
set @counter = 0
UPDATE Attribute_header 
SET @counter = HeaderOrder = @counter + 1
Where headerID IN (
	SELECT  distinct p.headerid 
	FROM attribute_header p
	WHERE Headeryearend > 2003)

Example of the records are:


HeaderID       HeaderYearEnd       HeaderOrder
1                   2005              2
2                   2005              1
3                   2005              3
4                   2003              -
5                   2005              4

If a transaction leads to deleting the 3rd record, then I have the following Records:

HeaderID       HeaderYearEnd       HeaderOrder
1                   2005              2
2                   2005              1
4                   2003              -
5                   2005              4

The way I want the query to update the records is (maintain the previous order as far as it is continuous and push the records up when a particular HeaderOrder is missing; i.e, change the HeaderOrder for record HeaderID=5 from 4 to 3):


HeaderID       HeaderYearEnd       HeaderOrder
1                   2005              2
2                   2005              1
4                   2003              -
5                   2005              3

It occured to me to put 'Order By HeaderOrder' at the end of the Select statement, but the query doesnot let me 'Order by HeaderOrder' since it is an Update statement. 

How Do I achieve this?

Thank you.
PKS.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 13:44:46

do you have the header id of the record that was deleted??

For you example


Declare @deletedId int
Set @deletedId = 3

UPDATE Attribute_header 
SET 
	HeaderOrder = convert(int,HeaderOrder)-1
Where HeaderOrder > (Select headerOrder From Attribute_header Where HeaderId = @deletedId)

Corey

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-07 : 13:52:33

Thanks for the suggestion Corey. But I might have multiple records that are deleted. I am not sure of the number of missing records in each transaction.

Do you have any suggestion which is more generic?

Thank you.
PKS.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 13:55:49

how are they getting deleted, do you not know which ones are getting deleted? I could generalize it to a set of variable length if you will know which ones are being deleted

Corey

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-07 : 14:10:25

I have no control over the way the records are deleted. The way it works I believe is that the user deletes the records that they do not want. I am stuck with updating the HeaderOrder as a one time process every day.

Thank you for the suggestions. What I want is a way to Order the records in the Select statement by HeaderOrder instead of the default Ordering.

Thank you.
PKS.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 14:14:40

does this help??


Declare @myTable table (id int, orderId int)
Insert Into @myTable 
Select 1, 3
Union All Select 3, 5
Union All Select 4, 2
Union All Select 6, 6
Union All Select 7, 0
Union All Select 9, 9


Select 
	id,
	orderId,
	newOrder = (Select count(*) From @myTable Where orderId < A.orderId) 
From @myTable A

Corey

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-07 : 14:18:47

I just figured out a way. I am selecting the records into a temporary table and ordering them by HeaderOrder. Then I am using the same update statement I have with records from the temporary table. That works just the way I want.

Thank you for your suggestions. Apprecite it.

Thanks again.
PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources