Update in an Order By series

Question

I need to run an UPDATE statement over multiple records in an ORDER BY series. This is because my update includes a scalar function, which uses the early updated value. Can anyone suggest a better way other than looping through in the desired order?

Sorry I should have done it very earlier, The Scenario is..

My problem was I have a table variable and it has hierarchical records,

ID SlNo result

1 1.0 True

2 1.1 True

3 1.1.1 True

4 1.1.2 False

5 1.2 True

6 1.2.1 False

7 1.2.2 False

8 1.2.3 False

9 1.2.4 False

And the tree structure is,

1.0

1.0 > 1.1

1.0 > 1.1 > 1.1.1

1.0 > 1.1 > 1.1.2

1.0 > 1.2

1.0 > 1.2 > 2.2.1

1.0 > 1.2 > 2.2.2

1.0 > 1.2 > 2.2.3

1.0 > 1.2 > 2.2.4

I want to update the results to a table and if all the child branches are "False" then its parent should go as "False". For that I need to update rows in the "descending Order" and also must see all its child branches are "False". Can i do it in a single update statement? How can I say Order by in My Update Query?

Any help? http://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql — Morphed, May 14 '12 at 09:23
You could calculate your new values in a SELECT statement, then use the result set to update the real table. — Andriy M, May 14 '12 at 11:41
What exactly should be updated based on what? And please do *not* just respond in a comment, but update your question instead (there's an `edit` link under the post's body). — Andriy M, May 14 '12 at 11:46

score 2 · Accepted Answer · answered May 14 '12 at 13:26

Write a query that produces the values that you want using a select. This query will probably make use of the row_number() function to ensure ordering.

Then, use this query for the update.

As a simple example, if you want to update a column to be a sequence number for a table, where the order is specified by col1, then you would do:

select t.*, row_number() over (partition by NULL order by col1) as seqnum
from table t

Now put this into the update:

with newvals (select t.*,
                     row_number() over (partition by NULL order by col1) as seqnum
              from table t
             )
update table
    set column = seqnum
from t
where t.id = table.id

Update in an Order By series

ID SlNo result

1 Answers1