CODEWITHSUNDEEP
sqlsql-serverdatabase
Alex Flores
Alex Flores

Reputation: 11

Change a value of duplicate data in SQL Server

I have a table Employees but here I have duplicated names and I would like to keep just one of the duplicate names (the one that has the biggest ID), and for the other ones, I need to change the isValid status to 0.

employees table

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use update:

with toupdate as (
      select e.*, max(id) over (partition by name) as max_id
      from employees
     )
update toupdate
    set isvalid = (case when id = max_id then 1 else 0 end);

You can also use a where, if you know that all the isvalid flags are set to 1:

update toupdate
    set isvalid = 0
    where id < max_id;

Upvotes: 2

Related Questions