Solved

Delete t-sql with inner join and where not exists

Posted on 2006-07-06
2
6,763 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Convert rows to columns 5 34
SQL Lag Function DateDiff 2 26
SQL Simple Query Taking a Very Long Time 11 37
Need SQL Query improved for multiple column joins 2 11
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

751 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