[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6788
  • Last Modified:

Delete t-sql with inner join and where not exists

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
JakeyCakes
Asked:
JakeyCakes
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now