Please help me to find a solution.
My requirement is similar to this.
Example:
TABLEA(pk_id, pol_mas_id,pol_seq_id)
values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3
Now i need to update the column "pol_seq_id" as below.
pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3
Currently i am using cursor and you know performance sucks.
Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).
Thanks in Advance!!
Try:
select
pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id
from
dbo.t1
go
It is helpful to have an index by (pol_mas_id, pk_id)
AMB
|||Thanks a lot for the response !!!It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.
It works
|||
Try using a CTE.
;with cte
as
(
select
pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn
from
dbo.t1
go
)
update cte
set pol_seq_id = rn;
AMB
