Deleting records with a join in the criteria

[emozley]

Hi there,

I am trying to delete records from a table called ScheduleDocuments but I don't want to touch the Documents table - I'm just using that to check the FolderID. If I do a SELECT statement it shows the records I want to to delete but when I try and do a DELETE statement it says there is an error.

Where am I going wrong please?

DELETE 
FROM ScheduleDocuments
LEFT JOIN Documents ON ScheduleDocuments.DocID=Documents.DocID 
WHERE ScheduleDocuments.ScheduleID=6 
AND Documents.FolderID=123

Thanks!

Ed

 

[bborissov]

DELETE FROM ScheduleDocuments
FROM ScheduleDocuments
INNER JOIN Documents ON ScheduleDocuments.DocID=Documents.DocID AND Documents.FolderID=123
WHERE ScheduleDocuments.ScheduleID=6

Borislav Borissov
VFP9 SP2, SQL Server