CODEWITHSUNDEEP
sqlsql-serverstored-procedures
eagertoLearn
eagertoLearn

Reputation: 10162

Stored procedure to update rows in a table that have duplicate value in SQL Server

I have a table active_user with 4 columns in a SQL Server database. I want a stored procedure to clean up data.

In the data shown below, I want to update isActive to 0 for all unique (firstName,lastName, isActive) where isActive = 1 and lastModified < the lastModified of (firstName, lastName, isActive) grouped orderedBy Desc Top 1.

Sample data before executing the stored procedure:

firstName lastName  isActive  lastModified
-----------------------------------------------------
foo         bar        1       2019-11-16 06:43:19.77
foo         bar        1       2018-11-16 06:43:19.77
foo         bar        1       2017-11-16 06:43:19.77
foo         bar        1       2016-11-16 06:43:19.77
foo         bar        0       2015-11-16 06:43:19.77
foo         bar        0       2014-11-16 06:43:19.77
foo1        bar1       1       2019-11-16 06:43:19.77
foo1        bar1       1       2018-11-16 06:43:19.77
foo1        bar1       0       2017-11-16 06:43:19.77
foo2        bar2       1       2018-11-16 06:43:19.77
foo2        bar2       0       2017-11-16 06:43:19.77

Same dataset, after executing the stored procedure:

firstName lastName  isActive  lastModified
------------------------------------------------------
foo         bar        1       2019-11-16 06:43:19.77
foo         bar        0       2018-11-16 06:43:19.77 //updated
foo         bar        0       2017-11-16 06:43:19.77 //updated
foo         bar        0       2016-11-16 06:43:19.77 //updated
foo         bar        0       2015-11-16 06:43:19.77
foo         bar        0       2014-11-16 06:43:19.77
foo1        bar1       1       2019-11-16 06:43:19.77 
foo1        bar1       0       2018-11-16 06:43:19.77 //updated
foo1        bar1       0       2017-11-16 06:43:19.77 
foo2        bar2       1       2018-11-16 06:43:19.77
foo2        bar2       0       2017-11-16 06:43:19.77

In total 4 rows updated.

Here is my attempt in pseudo SQL:

update active_user 
set isActive = 0 in (SELECT unique(firstName, lastName, isActive) 
where isActive = 1 
  and lastModified < (select TOP 1 distinct(firstName, lastName, isActive) 
                      where isActive = 1) 
group by (firstName, lastName, isActive) orderBy))

I am looking for how to approach this problem so it can be done elegantly.

Upvotes: 2

Views: 162

Answers (2)

Squirrel
Squirrel

Reputation: 24813

use row_number to identify the row with latest lastModified value and set to 1. The rest to 0

; with cte as
(
    select *, rn = row_number() over (partition by firstName, lastName  
                                          order by lastModified desc)
    from   active_user
)
update c
set    isActive = case when rn = 1 then 1 else 0 end
from   cte c

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this in SQL Server using an updatable CTE:

with toupdate as (
      select au.*,
             row_number() over (partition by first_name, last_name, isactive order by last_modified desc) as seqnum
      from active_user au
     )
update toupdate
    set isActive = 0
    where seqnum <> 1 and isActive = 1;

Upvotes: 3

Related Questions