search and deploy


I need to update the first x numbers from a table and am using a ROWCOUNT like:

SET ROWCOUNT @OrderSize

UPDATE table1

SET Assigned = GETDATE(), ReceiverID=@ReceiverID

WHERE ReceiverID=0 AND Number>=@FirstNumber

Now most of the time this is fine but sometimes the "Number" s don't come in order, which is a requirement. Adding

ORDER BY Number

is not allowed. How do I force the records to be set in Number order




Re: ordered update

richbrownesq


Try:

UPDATE table1
SET Assigned = GETDATE(), ReceiverID = @ReceiverID
WHERE ReceiverID = 0 AND Number IN
(SELECT TOP 100 Number FROM table1 WHERE Number >= @FirstNumber ORDER BY Number ASC)






Re: ordered update

DaleJ

Data is not guaranteed to have any specific order unless the ORDER BY is applied.

Why is ORDER BY not allowed

Maybe this will do it:

Code Snippet

SET ROWCOUNT @OrderSize

UPDATE table1

SET Assigned = GETDATE(), ReceiverID=@ReceiverID

WHERE ReceiverID=0

AND Number BETWEEN @FirstNumber AND (@FirstNumber + @OrderSize)







Re: ordered update

hunchback

Which version of SS are you using

-- 2005

;with cte

as

(

select *, row_number() over(order by Number) as rn

from table1

where ReceiveID = 0

)

update cte

set Assigned = getdate(), ReceiveID = @ReceiveID

where rn <= @OrderSize;

AMB





Re: ordered update

theking2


Code Snippet
UPDATE tbl423663
SET ReceiverID=0
, DistriOrderID=null
, Assigned=GETDATE()
WHERE ReceiverID = 0 AND Number >= '447937003000'
ORDER BY Number

Results in:

Code Snippet
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ORDER'.

The trick looks quite ok but unfortunately is not. WIth @FirstNumber + @OrderSize this will only work if a) numbers are consecutive (which is not garantueed) and b) there are no ReceiverID <> 0 in the range wich is definitily not garantueed).

but thanks





Re: ordered update

theking2

Thanks richbrownesq, almost right :-)

the ReceiverID = 0 test should be in the nested select other wise less than or equal 100 records will be updated.

Code Snippet

UPDATE table1
SET Assigned = GETDATE(), ReceiverID = 0
WHERE ReceiverID = 0 AND Number IN
(SELECT TOP 100 Number FROM table1 WHERE Number >= '447937000000' ORDER BY Number ASC)

However this fixes the number of records (order size) to 100 whereas it could be anything 1-50000.

So the trick is to create a temp table:

Code Snippet

SET ROWCOUNT @OrderSize
SELECT Number INTO #OrderedNumber FROM table1 WHERE ReceiverID = 0 AND Number >= @FirstNumber ORDER BY Number ASC

UPDATE table1

SET Assigned = GETDATE(), ReceiverID = 0
WHERE Number IN (SELECT Number FROM #OrderedNumber)

But thanks for your help. I was pointed in the right direction.

TheKing2




Re: ordered update

DaleJ

Why is ORDER BY not allowed






Re: ordered update

theking2

see previous reply



Re: ordered update

DaleJ

I guess I'm all turned around.

Looks like you could use ORDER BY after all...






Re: ordered update

theking2

Hi DaleJ,

ORDER BY is only allowed in the SELECT statement not in the UPDATE statement.

Therefor a temp table is used to store the ordered top @OrderSize records and this temporary table is used to update the table. It is not possible to do this in one UPDATE statement.

I hope this clears up the confusion....





Re: ordered update

theking2

Thanks Hunchback,

This does not remotely look like the T/SQL I know :-) cte over there is so much to learn.

Needless to say I'm on SQL2000 as 2005 lacks support for DTS and we have more than 100 Local Packages defined that all need to be migrated and tested. Something our company will not do (unless MS pays us :-) or hell freezes over, whatever comes first)





Re: ordered update

DaleJ

My misunderstanding....

Thanks.






Re: ordered update

hunchback

Can you give us more info about your table

- Does it have a primary key

- Does column [Number] allow duplicated entries

- In case of duplicated entries for [Number], which column can we use as a tie breaker

update

table1

set

table1.Assigned = getdate(),

table1.ReceiveID = @ReceiveID

from

(

select top 5

*

from

(

select

*,

(

select

count(*)

from

table1 as b

where

b.ReceiveID = 0 and b.Number <= a.Number

) as rn

from

table1 as a

where

ReceiveID = 0

) as t1

order by

rn ASC

) as t2

where

table1.Number = t2.Number

go

AMB





Re: ordered update

Umachandar Jayachandran - MS

The ONLY way to perform ordered updates is to use a cursor. UPDATE statement is a set-based operation and there is no guarantee for the order of updates. It can change based on the query plan, data, indexes etc. Having said this, you can do it with the UPDATE statement by generating the after image (or post-update values) and joining with the rows in the UPDATE statement. Can you post some sample data and expected results