-3

I wonder is the following query valid in sql: 

DELETE FROM Reporters 
JOIN Cases ON Reporters.CaseId = Cases.ID 
WHERE Cases.Court = @Court

I get an error:

Incorrect syntax near join

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ppetyr
  • 111
  • 1
  • 10
  • Are you using MS SQL Server? If so, your question is a duplicate of https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server – jpw Sep 29 '16 at 13:22
  • 3
    Which DBMS are you using? Standard SQL does not support a JOIN with a DELETE statement –  Sep 29 '16 at 13:23

4 Answers4

1
DELETE r
FROM reporters as r
INNER JOIN cases as c
ON r.CaseId = c.ID 
    WHERE c.Court = @Court
gh9
  • 10,169
  • 10
  • 63
  • 96
1

Yes you can, you just have to specify Alias to your table and delete using it:

DELETE R
-- SELECT *
FROM Reporters AS R
INNER JOIN Cases AS C
    ON R.CaseId = C.ID
WHERE C.Court = @Court ;
Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
1

It will work if you alias the tables like;

DELETE r
FROM Reporters AS r
INNER JOIN Cases AS c
    ON r.CaseId = c.ID
WHERE c.Court = @Court

Or you could use

DELETE FROM Reporters 
WHERE EXISTS (SELECT 1 FROM Cases WHERE Cases.ID = Reporters.CaseId AND Cases.Court = @Court)
Milney
  • 6,253
  • 2
  • 19
  • 33
1

mention from which table that you wanted to delete from. (the solution is specific to SQL Server)

DELETE re --if you wanted to delete the cases replace `re` with `ca`
FROM Reporters re 
JOIN Cases ca ON re.CaseId = ca.ID 
    WHERE ca.Court = @Court
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21