Re: Advice about how to delete

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