Delete - when no rows

johnkainn
johnkainn used Ask the Experts™
on
I have 2 tables.  GTable (GId, GText)   CTable (CId, GId, CText)
I would like to delete row from GTable when there are no rows in CTable that refer to GTable.
For example. If there are 2 rows in CTable with GId=4 no action.
But if there are 0 rows with GId=5 I would like to delete from GTable the row GId=5.
How is best to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
DELETE G
FROM GTable G
      LEFT OUTER JOIN CTable C ON G.GId = C.GId AND G.CText = C.CText
WHERE C.CId IS NULL      
I am assuming that you will supply GID as input to delete

declare @GID int

set @GID = 3  -- Or your inputed value

delete from GTable where GId = @GID and not exists(select 1 from CTable where GId = @GID)

delete from GTable where GId = @GID and not exists(select 1 from CTable where GId = @GID)


--if you want to delete full table then use this command

delete from GTable where GId not in (select GID from CTable)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial