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)