May 20, 2011 at 6:33 pm
I'm using SQL Server Express 2008 R2. I've been looking through BOL but cannot find an example or explanation.
Why does this work:
SELECT * FROM CurIndividual
LEFT JOIN CurAccounts ON CurIndividual.MemberID = CurAccounts.MemberID
WHERE CurAccounts.MemberID IS NULL
-returns 33 rows. Same number I get in Access.
but not this?
DELETE FROM CurIndividual
LEFT JOIN CurAccounts ON CurIndividual.MemberID = CurAccounts.MemberID
WHERE CurAccounts.MemberID IS NULL
I get "syntax error near 'LEFT'."
I'm trying to get rid of 33 orphaned records in the CurIndividual table. CurAccounts is the parent table. LEFT OUTER JOIN returns no rows. So obviously neither syntax is valid. Any ideas?
Thanks.
-Michael
May 21, 2011 at 9:09 am
Try this:
DECLARE @Parent TABLE
(
ParentID INT
)
DECLARE @child TABLE
(
ChildID INT,
ParentID INT
)
-- Inserting Sample Data
INSERT INTO @Parent
SELECT SPT.number AS ParentID
FROM master.dbo.spt_values SPT
WHERE SPT.number BETWEEN 1 AND 8
AND SPT.type = 'P'
INSERT INTO @child
SELECT DISTINCT SPT.number AS ChildID ,
SPT.number AS ParentID
FROM master.dbo.spt_values SPT
WHERE SPT.number BETWEEN 1 AND 20
AND SPT.type = 'P'
-- SELECT THE VALUES
SELECT C.ChildID
FROM @child C
LEFT JOIN @Parent P
ON P.ParentID = C.ParentID
WHERE P.ParentID IS NULL
-- This will be your syntax to delete orphan rows
-- orphan - rows that dont have a matching parent
DELETE C
FROM @child C
LEFT JOIN @Parent P
ON P.ParentID = C.ParentID
WHERE P.ParentID IS NULL
SELECT * FROM @child
You can perform DELETEs using JOINs.. it was a syntax error in your code 🙂
HTH 😉
May 21, 2011 at 9:22 am
DELETE CurIndividual FROM CurIndividual
LEFT JOIN CurAccounts ON CurIndividual.MemberID = CurAccounts.MemberID
WHERE CurAccounts.MemberID IS NULL
Can u try this....
May 21, 2011 at 4:36 pm
sqlzealot-81,
Thank you. That worked perfectly. 33 rows deleted, as expected.
May 21, 2011 at 5:50 pm
The issue is that the DELETE statement supports two FROM clauses. To make it even better, the first FROM keyword is optional. It is with the second FROM clause where you can join to other tables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply