DELETE SUBQUERY
485900Oct 26 2006 — edited Oct 26 2006I am trying to do a delete which really requires a join and I'm not sure how to do it.
Here is my scenario, say I have the following two tables:
create table items
(
id INT,
Name varchar2(50)
)
create table ItemAssign
(
itemid INT,
userid INT
)
There is a one to many relationship between items and ItemAssign, hence more than one person can be assigned to an item.
Now I want to delete all items assigned to a particular person but ONLY if they are the only person assigned to the item.
Here is query which performs very badly:
DELETE FROM items
WHERE id IN (SELECT itemid
FROM ItemAssign
WHERE (userid= 1)) AND (NOT EXISTS
(SELECT itemid
FROM ItemAssign IA
WHERE (items.id = IA.itemid) AND (IA.userid <> 1)))
Is there another way to write this query?