Syringe.Net.Nz
Irregular Injection of Opinion
RSS 2.0|Atom 1.0|CDF

 Tuesday, July 08, 2008
Controlling the update order in T-SQL on SQL Server 2005

Sometimes you just want to do this

update foo set bar=123 order by foo.lastmodifiedtimestamp

Why.... well you may rely on the order of the timestamp.

It's suprprisingly hard to do but is possible using CTEs.

WITH InvoicesNumbered AS

(

SELECT *, ROW_NUMBER() OVER(ORDER BY LastModifiedTimestamp desc) AS RowNum

FROM Invoice

)

UPDATE InvoicesNumbered

set createdby =

(select top 1 userID from [user] where clientid = InvoicesNumbered .invoiceclientid)

WHERE RowNum < 20000; --Use a number larger than the row count of the table.

Idea pinched from here. Reposted in search of better Live Search ranking for the obvious search query 'controlling update order in SQL Server'

.NET|Tuesday, July 08, 2008 9:12:25 AM UTC|Comments [0]|