CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Hidalgo
Hidalgo

Reputation: 941

How to SQL UPDATE in a certain order

I want to learn how to process table rows when updating column(s), in a certain order (and not in the random or in the order that the records were created).

To illustrate my question, in the made-up example below I want to set the Ranking according to the date_time such that the older records are processed first (and get lower ranking).

create table #testTable (customer_id char(20), ranking int, date_time
Datetime, pk_value int identity )
go

insert into #testTable (customer_id, date_time) values 
('MICROSOFT', '20110202')
insert into #testTable (customer_id, date_time) values 
('MICROSOFT', '20120202')
insert into #testTable (customer_id, date_time) values
('MICROSOFT', '20090512')
go
declare @Rank int
set @Rank = 0
update #testTable set @Rank = @Rank + 1, ranking = @Rank

Upvotes: 0

Views: 627

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

It looks like you are using SQL Server. If so, you can set the ranking using the (appropriately named) ranking functions:

with toupdate as (
      select t.*, row_number() over (order by date_time) as seqnum
      from #testtable t
     )
update toupdate
    set ranking = seqnum;

Upvotes: 3

Related Questions