CODEWITHSUNDEEP
sqloracle-database
jojoberry
jojoberry

Reputation: 13

Updating records in a specific order

I am trying to update all records in my table. As I read through the records I need to update a column in the current record with a value from the NEXT record in the set. The catch is the updates need to be done in a specified order.

I was thinking of something like this ...

Update t1
Set col1 = (select LEAD(col2,1) OVER (ORDER BY col3, col4, col5)
            from t1);

This doesn't compile but you see what I'm driving at ... any ideas ?

... update

This peice does run successfully but writes only NULLS

Update t1 A
Set t1.col1 = (select LEAD(col2,1) OVER (ORDER BY col3, col4, col5)
           from t1 B
           where A.col3 = B.col3 AND
                 A.col4 = B.col4 AND
                 A.col5 = B.col5);

Upvotes: 1

Views: 205

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this using the with statement:

with toupdate as (
    select t1.*,
           lead(col2, 1) over (order by col3, col4, col5) as nextval
    from t1
)
Update toupdate
    Set col1 = nextval

By the way, this does not guarantee the ordering of the updates. However, col2 is not mentioned in the partitioning clause so it should do the right thing.

The above syntax works in SQL Server, but not Oracle. The original question did not specify the database (and lead is a valid function in SQL Server 2012). It seems the merge statement is the way to get the values in the subquery.

Upvotes: 0

user330315
user330315

Reputation:

This should do it:

merge into t1
using
(
   select rowid as rid, 
          LEAD(col2,1) OVER (ORDER BY col3, col4, col5) as ld
   from t1
) lv on ( lv.rid = t1.rowid )
when matched then 
  update set col1 = lv.ld;

Not 100% sure if I got the syntax completely right, but as you didn't supply any testdata, I'll leave potential syntax errors for you to fix.

You can also replace the usage of rowid with the real primary key columns of your table.

Upvotes: 1

Luke Liu
Luke Liu

Reputation: 298

Why don't you use cursor? You can use update within a cursor with specified order.

Upvotes: 0

Related Questions