CODEWITHSUNDEEP
sqlsql-serversql-update
Emrah Sumer
Emrah Sumer

Reputation: 13

MS SQL: update only the last record of each group

I have a table in MS SQL Server that has some unique and duplicate records.

Let's say my table is like

Name   Modified    Visibility   UserTypeEx
x      24.05.2015      1           4096
y      01.01.2014      0           4096
z      01.04.2016      1           4096
x      05.03.2015      1           4096
y      06.08.2015      1           4097
y      05.07.2014      1           4096

As you can see, I have duplication in the name column. What I am trying to do is to update all visibility fields to 0 where the UserTypeEx is 4096, except that where there are duplicate names I want to update only the last-added record among those in each group sharing a name.

Such an update would transform the example data to this:

Name   Modified    Visibility   UserTypeEx
x      24.05.2015      0           4096
y      01.01.2014      0           4096
z      01.04.2016      0           4096
x      05.03.2015      1           4096
t      06.08.2015      1           4097
y      05.07.2014      0           4096

Any suggestions?

Upvotes: 1

Views: 404

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In SQL Server, you can use window functions with update. This is highly convenient:

with toupdate as (
      select t.*,
             row_number() over (partition by name order by modified desc) as seqnum,
             count(*) over (partition by name) as cnt
      from t
      where UserTypeEx
     )
update toupdate
    set visibility = 0
    where cnt > 1 and seqnum = 1;

Upvotes: 2

Related Questions