Sarat February 2016

How to update column of duplicate row in SQL Server

I have a table tUser with columns username, password, email, Active.

I need to set the Active column of duplicate rows to 0. How can I achieve this?

Eg: Table values are

UserName    Password   Email             Active
-----------------------------------------------
Demo        demo       abc@gmail.com       1
Demo        demo       abc@gmail.com       1
Demo        demo       abc@gmail.com       1

I need to change this value to

Demo demo abc@gmail.com 1

and the other 2 rows should be

Demo demo abc@gmail.com 0
Demo demo abc@gmail.com 0

Answers


marc_s February 2016

Once you have a primary key in your table that uniquely identifies each row, then you can use a CTE and this approach to weed out any duplicates:

;WITH Duplicates AS
(
    SELECT
        UserID, Username, Password, Email, Active,
        RowNum = ROW_NUMBER() OVER (PARTITION BY Username, Email ORDER BY UserID)
    FROM
        tUser
)
UPDATE tUser
SET Active = 0
FROM Duplicates d
WHERE d.UserID = tUser.UserID
  AND d.RowNum > 1

Post Status

Asked in February 2016
Viewed 3,943 times
Voted 5
Answered 1 times

Search




Leave an answer