CODEWITHSUNDEEP
sqlsql-delete
Nensi Dupuy
Nensi Dupuy

Reputation: 7

Deleting Records Based on Criteria

I have a list of users (Field = UserName) that I got from a table (Users). Some are duplicates.

Where there is a duplicate record, I need to delete the most current record (CreatedOn) created.

Also, if it trips up on the ability to delete because there are 'relationships established to this user ID' in the database, I need it to skip to the next record and continue deleting.

How do I accomplish this??

Upvotes: 0

Views: 83

Answers (4)

Donald
Donald

Reputation: 542

DELETE  from  user u
WHERE   u.UserId in ( SELECT Distinct userid from User u join User u2 where u.UserName = u2.UserName
and u.CreatedOn > u2.CreatedOn)

You can't skip on error in one sql, but you could exclude userid that exists in related tables.

Upvotes: 0

MattBosten
MattBosten

Reputation: 41

Adding on from Diego's answer with a check that the UserName is not the oldest instance in the table. 

DELETE FROM 
    Users
WHERE 
    UserName IN 
    (SELECT UserName FROM Users GROUP BY UserName HAVING COUNT(UserName) > 1)
    AND
    CreatedOn != 
    (SELECT MIN(CreatedOn) FROM Users T1 WHERE UserName = T1.UserName);

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Use EXISTS to remove a row if same UserName also has an older CreatedOn:

delete from users u1
where exists (select 1 from users u2
              where u2.UserName = u1.UserName
                and u2.CreatedOn < u1.CreatedOn)

Or, another approach:

delete from users
where (UserName, CreatedOn) not in (select UserName, MIN(CreatedOn)
                                    from users
                                    group by UserName)

Upvotes: 0

dvaltrick
dvaltrick

Reputation: 122

You can try something like this:

Delete from Users
 where nameuser in 
(select nameuser from Users
Group by nameuser
Having count(nameuser) > 1)

Upvotes: 0

Related Questions