Solved

Delete t-sql with inner join and where not exists

Posted on 2006-07-06
2
6,772 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

634 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