Hi,
I have a join table called usersgroups between two tables called users and groups. The primary key of the usergroups table is a composite key of the userid and groupid from the users and groups tables respectively. The users table has a parent/child relationship with userid and parent_userid columns. I am removing the child rows to only retain the parent entries and flatten the relationship. I want to update the usersgroups table to ensure that the remaining user entry (existing parent) is in all the groups the child entries was in i.e. switch the userid from the child to the parent where necessary in usersgroups. It is possible the parent will already be a member of some of the same groups so I want to filter them out in the update as this will break the primary key constraint.
I have tried various methods but going around in circles. I have tried:
UPDATE usersgroups i
SET userid =
(SELECT u.parent_userid
FROM users u, groups g
WHERE u.userid = i.userid
AND i.groupid = g.groupid
AND u.parent_userid IS NOT NULL
)
WHERE EXISTS
(SELECT 1
FROM users u, groups g
WHERE u.userid = i.userid
AND i.groupid = g.groupid
AND u.parent_userid IS NOT NULL
);
But this doesn't filter out the the updates that will cause a constraint error. I have tried adding a NOT EXISTS clause and joining on the parent_userid, roleid combination as that ultimately will be what is updated but that doesn't help i.e. if the update of userid with the parent_userid would result in the userid, roleid composite key matching a combination already in the table don't update the table with it.
I'm thinking it might be quicker (although dirtier) to disable all constraints, do the update, remove duplicates and re-enable the constraints. I'd rather not do this as I'm sure it has to be possible to do this in SQL and for the purposes of learning I would like to understand where I am going wrong.
All help greatly appreciated.
David