CODEWITHSUNDEEP
sqlsql-server
Robin clave
Robin clave

Reputation: 638

Update on non duplicate records in sql server

I have a table with 4 columms like below

var1    var2    var3    ran
a       a       a       1
b       a       a       2
c       a       a       3
d       b       b       4
e       c       c       1

Here based on the value in columns var2 and var3, it is consider as duplicates. 4th column is the row number for the duplicates. I need to re-initialise the row-number (update) on non-duplicate records.

Expected result will be (note that d b b 4 became d b b 1):

var1    var2    var3    ran
a       a       a       1
b       a       a       2
c       a       a       3
d       b       b       1
e       c       c       1

Upvotes: 0

Views: 171

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

declare @T table
(
  var1 char(1),
  var2 char(1),
  var3 char(1),
  ran int
)

insert into @T values
('a', 'a', 'a', 1),
('b', 'a', 'a', 2),
('c', 'a', 'a', 3),
('d', 'b', 'b', 4),
('e', 'c', 'c', 1)

;with C as
(
  select ran,
         row_number() over(partition by var2, var3 order by var1) as rn
  from @T
)
update C 
set ran = rn

select *
from @T

Result:

var1 var2 var3 ran
---- ---- ---- -----------
a    a    a    1
b    a    a    2
c    a    a    3
d    b    b    1
e    c    c    1

Upvotes: 2

Related Questions