0

I have a table as follows, I want to automatically increase the SIRANO value according to the rows with the same MF_ID value in this table.

SIRANO MF_ID
NULL 30
NULL 30
NULL 30
NULL 31
NULL 31
NULL 32

must be:

SIRANO MF_ID
1 30
2 30
3 30
1 31
2 31
1 32 
DECLARE @counter int
SET @counter = 0
update A 
set SIRANO = (case when A.MF_ID=B.MF_ID then @counter+1
                           else 0
                           end)
FROM MYTABLE_0002 a 
INNER JOIN MYTABLE_0002 b
on a.MF_ID = b.MF_ID
Thom A
  • 88,727
  • 11
  • 45
  • 75
Yusuf
  • 1
  • Wouldn't an `UPDATE`able CTE with a `ROW_NUMBER` achieve this? – Thom A Apr 18 '23 at 08:37
  • I want it to do this automatically in a large table, the query I wrote did not provide this :( – Yusuf Apr 18 '23 at 08:40
  • 1
    you can use this is query,first you must made a small query with limited result for example "where MF_ID=@" ;With UpdateData As ( SELECT MF_ID, ROW_NUMBER() OVER (partition by MF_ID ORDER BY MF_ID DESC) AS RN,SIRANO FROM T ) UPDATE T SET SIRANO = RN FROM UpdateData T – abolfazl sadeghi Apr 18 '23 at 10:14

0 Answers0