On Jul 6, 2007, at 9:42 , Arnau wrote:
I have the following scenario, I have users and groups where a
user can belong to n groups, and a group can have n users. A user
must belogn at least to a group. So when I delete a group I must
check that there isn't any orphan. To do this I have something like
that:
IF v_count = 1 THEN
DELETE FROM users WHERE user_id = result.user_id;
v_deleted = v_deleted + 1;
END IF;
Am I right in reading that you're deleting any users that would be
orphans? If so, you can just delete the orphans after rather than
delete them beforehand (untested):
-- delete user_group
DELETE FROM user_groups
WHERE user_group_id = p_group_id;
-- delete users that don't belong to any group
DELETE FROM users
WHERE user_id IN (
SELECT user_id
LEFT JOIN user_groups
WHERE group_id IS NULL);
This should execute pretty quickly. You don't need to loop over any
results. Remember, SQL is a set-based language, so if you can pose
your question in a set-based way, you can probably find a pretty
good, efficient solution.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
