How to delete a record when a user has two records with the following condition-CodePudding

I need to clean up some data in a table. In my data, a user can have multiple grants and if they have grant_id = 7 and grant_id = 5, we want to delete their grant_id = 7 record (and leave extraneous records intact). How would I do this?

I was thinking a subselect like:

# select
select * from grants.user_grants where grant_id = 7 
and user_id in (select user_id from grants.user_grants where grant_id = 5);

# delete
delete from grants.user_grants where grant_id = 7 
and user_id in (select user_id from grants.user_grants where grant_id = 5);

but suspect a more elagant way

delete the second row, keep the 4th row

user_id	grant_id
2	5
2	7
3	5
3	11

CodePudding user response：

SELECT distinct(tblTest.user_id), Max(tblTest.grant_id) AS MaxOfgrant_id FROM tblTest GROUP BY tblTest.user_id;

returns

user_id MaxOfgrant_id

2 7

3 11

CodePudding user response：

Subqueries are for this purpose:

delete from grants.user_grants ug
where exists (
              /* find if there is another row with the same user_id
                 but lower grant_id */
              select *
              from grants.user_grants ugwlowg
              where ugwlowg.user_id=ug.user_id
                and ugwlowg.grant_id < ug.grant_id)