SQLTeam.com | Weblogs | Forums

Delete Statement


#1

Hi,

What is wrong below?

DELETE FROM dbo.Referral INNER JOIN 
DISSENTED_PATS ON dbo.Referral.PatientGuidDigest = DISSENTED_PATS.PatientGuidDigest

Thanks


#2

The statement is syntactically incorrect. The reason for that is that a delete statement can delete from only one table at a time. The way the code is written, it is not clear to SQL Sever whether you want to delete from dbo.Referral or from DISSENTED_PATS or from both. The right way to write it, assuming you want to delete from dbo.Referral would be as follows (where optionally I have aliased the tables to single letter aliases)

DELETE r 
FROM 
dbo.Referral r INNER JOIN 
DISSENTED_PATS d ON r.PatientGuidDigest = d.PatientGuidDigest