I have two tables reminder and reminder_users, between these tables there is a foreign key constraint and reminder_users are the child records. What I want to do is to delete several redundant rows in these tow tables. What I managed to do so far is to find the redundant rows with the following query:
select r.name, r.remark, u.user_id, u.deadline , count (*)
from reminder r
inner join reminder_users u on r.id = u.reminder_id
and u.user_id = u.user_id
and u.deadline = u.deadline having count(*)> 1
group by r.name, r.remark, u.user_id, u.deadline
So the redundant rows are tied with the foreign key id and the child records have the same user_id and the same deadline, and the parent records have the same name and the same remark.
The having count clause point out the rows, that are redundant, but I want do keep one of the records so that having count > 1 is not true any more, so i can not use this clause in a delete-query. Also in order to be able to delete the rows from reminder, first the child data from reminder_users have to be deleted.
I guess that it should somehow be possible with the usage of the rowid and something like a select max (rowid), but i have no further ideas how to structure the delete-query.
Any help is much appriciated!
You're on the right track. You can delete duplicate rows by removing all those where their rowid is not one returned by the subquery:
delete reminder
where rowid not in (
select min ( r.rowid )
from reminder r
inner join reminder_users u
on r.id = u.reminder_id
and u.user_id = u.user_id
and u.deadline = u.deadline
group by r.name, r.remark, u.user_id, u.deadline
)
There's no need for the having clause in the subquery.
Note: this assumes that there's a row in reminder_users for every reminder. If this isn't the case, this will also remove the reminders without a user as the subquery will not return them.