Solved

Delete t-sql with inner join and where not exists

Posted on 2006-07-06
2
6,739 Views
Last Modified: 2008-01-09
Could someone have a look at my below delete statements and inform me how I could modify them (so that they work :)) to include the inner join and where not exists. At the moment I get the error 'Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'INNER'

My code:



DELETE
FROM          dbo.BTaxProprietor
INNER JOIN
                      dbo.BTaxBusiness ON dbo.BTaxProprietor.lBusinessID = dbo.BTaxBusiness.lBusinessID INNER JOIN
                      dbo.Partnership ON dbo.BTaxProprietor.lPartnershipID = dbo.Partnership.PClientid
WHERE NOT EXISTS(SELECT PClientid from PartnershipClients as pc
                 where  pc.PClientid = dbo.Partnership.PClientid)


DELETE
FROM         dbo.BTaxBusiness INNER JOIN
                      dbo.BTaxProprietor ON dbo.BTaxBusiness.lBusinessID = dbo.BTaxProprietor.lBusinessID INNER JOIN
                      dbo.Partnership ON dbo.BTaxProprietor.lPartnershipID = dbo.Partnership.PClientid
WHERE NOT EXISTS(SELECT PClientid from PartnershipClients as pc
                 where  pc.PClientid = dbo.Partnership.PClientid)

DELETE
FROM         dbo.BTaxAccountingPeriod INNER JOIN
                      dbo.BTaxProprietor ON dbo.BTaxBusiness.lBusinessID = dbo.BTaxProprietor.lBusinessID INNER JOIN
                      dbo.Partnership ON dbo.BTaxProprietor.lPartnershipID = dbo.Partnership.PClientid INNER JOIN
                      dbo.BTaxAccountingPeriod ON dbo.BTaxBusiness.lBusinessID = dbo.BTaxAccountingPeriod.lBusinessID
WHERE     (NOT EXISTS
                          (SELECT     PClientid
                            FROM          PartnershipClients AS pc
                            WHERE      pc.PClientid = dbo.Partnership.PClientid))
0
Comment
Question by:JakeyCakes
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17049124

DELETE       dbo.BTaxProprietor
FROM          dbo.BTaxProprietor  p
INNER JOIN dbo.BTaxBusiness b
  ON p.lBusinessID = b.lBusinessID
INNER JOIN dbo.Partnership pa
  ON p.lPartnershipID = pa.PClientid
WHERE NOT EXISTS (SELECT PClientid from PartnershipClients as pc
                 where  pc.PClientid = p.PClientid)

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question