Improve company productivity with a Business Account.Sign Up

x
?
Solved

Delete t-sql with inner join and where not exists

Posted on 2006-07-06
2
Medium Priority
?
6,791 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
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

595 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