5

I need to update a column with an sequential number starting with 1 based on the accountId in the table. How do I do this?

so OrderID is NULL to start. so for each row of AccountID I need to update the OrderID to start from 1 and sequentially update so my data comes out like this. The table is called Renewals

RowID  AccountID  OrderID
1      A          1
2      A          2
4      A          3
5      B          1
6      B          2
7      C          1
  • 1
    Why do you need to store this rank? All it does is require you to maintain it every time the table is touched. Can't you calculate this number at query time? – Aaron Bertrand Feb 18 '17 at 13:56
8

Using a common table expression with row_number() to partition by AccountId and order by [RowId]:

;with cte as (
  select *
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
update cte
  set OrderId = NewOrderId;

Without using the common table expression:

update r
  set OrderId = NewOrderId
  from (
    select *
      ,  NewOrderId = row_number() over (
            partition by AccountId
            order by [RowId]
      )
    from Renewals
      ) as r

test setup: http://rextester.com/FSUD49402

create table Renewals (
    [RowId]   int     not null
  , AccountId char(1) not null
  , OrderId   int         null
);
insert into Renewals (RowId, AccountId) values
  (1,'A'), (2,'A'), (4,'A')
, (5,'B'), (6,'B'), (7,'C');

with cte as (
  select 
       [RowId]
    ,  AccountId
    ,  OrderId
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)

update cte
  set OrderId = NewOrderId;

select * from Renewals;

results in:

+-------+-----------+---------+
| RowId | AccountId | OrderId |
+-------+-----------+---------+
|     1 | A         |       1 |
|     2 | A         |       2 |
|     4 | A         |       3 |
|     5 | B         |       1 |
|     6 | B         |       2 |
|     7 | C         |       1 |
+-------+-----------+---------+

To answer the question in the comment of this answer:

I am using this data to update the previous row. [...] I am trying to show that Row 1 above was renewed by row 2 was replaced by row 4 and row4 has not been renewed as yet

Can be done with:

select 
    r.*
  , RenewedBy=p.RowId 
from Renewals as r 
  left join Renewals as p 
    on p.AccountId = r.AccountId 
   and p.OrderId = r.OrderId+1

returns:

+-------+-----------+---------+-----------+
| RowId | AccountId | OrderId | RenewedBy |
+-------+-----------+---------+-----------+
|     1 | A         |       1 | 2         |
|     2 | A         |       2 | 4         |
|     4 | A         |       3 | NULL      |
|     5 | B         |       1 | 6         |
|     6 | B         |       2 | NULL      |
|     7 | C         |       1 | NULL      |
+-------+-----------+---------+-----------+

If this is the only reason for the previous update, we don't have to update the table at all. This will get the same results as above:

;with cte as (
  select *
    ,  rn = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
select 
    r.RowId
  , r.AccountId
  , OrderId = r.rn
  , RenewedBy=p.RowId 
from cte as r 
  left join cte as p 
    on p.AccountId = r.AccountId 
   and p.rn = r.rn+1
  • 1
    Everything but the quirky update ;) – Erik Darling Feb 18 '17 at 13:21
  • I am using this data to update the previous row. How do I attach an excel file so you can see what the actual output needs to be? I am trying to show that Row 1 above was renewed by row 2 was replaced by row 4 and row4 has not been renewed as yet – Chris Michalczuk Feb 19 '17 at 16:02
  • @ChrisMichalczuk That sounds like a different question entirely... But you could query the table with a self join like: select r.*, RenewedBy=p.RowId from Renewals as r left join Renewals as p on p.AccountId = r.AccountId and p.OrderId = r.OrderId+1; – SqlZim Feb 19 '17 at 16:17
  • I don't know if I should laugh or cry.. why is it so complicated to perform simple loops in SQL? It's as if nobody ever expected to iterate over data when SQL was designed, so now people are forced to find weird tricks to get it done. – metabuddy Oct 25 '18 at 12:27
0

Will this work for you?

set nocount on
Declare @Renewals table (RowId int, AccountId varchar(10), OrderId int)

insert into @Renewals (RowId, AccountId, OrderId) values (1,'A',null)
insert into @Renewals (RowId, AccountId, OrderId) values (2,'A',null)
insert into @Renewals (RowId, AccountId, OrderId) values (4,'A',null)
insert into @Renewals (RowId, AccountId, OrderId) values (5,'B',null)
insert into @Renewals (RowId, AccountId, OrderId) values (6,'B',null)
insert into @Renewals (RowId, AccountId, OrderId) values (7,'C',null)

UPDATE a
SET OrderId = (
        SELECT count(*) + 1
        FROM @Renewals
        WHERE AccountId = a.AccountId
            AND RowId < a.RowId
        )
FROM @Renewals a

SELECT *
FROM @Renewals
0

You can just create a view
It is efficient and you don't have to update Renewal with any change

create view vvv
as 
( select [RowId], [AccountId] 
       , row_number() over (partition by [AccountId] order by [RowId]) as [OrderId]
  from [Renewals] 
)

select * from vvv
  • SQLZIM you are a star - this does exactly what I want to achieve. Now just have to do the update in my master table – Chris Michalczuk Feb 23 '17 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.