DELETE using an INNER JOIN

Databases
1

I want to delete a row in one table by using and inner join to identify the row I want to delete.

This is what the tables look like:

participantevent table: participantid | eventid
district_participants table: id | name | districtid

I want to delete a row in the participantevent table using the districtid from the district_participants table

I have tried the follow:


DELETE FROM participantevent pe INNER JOIN district_participants dp on dp.id = pe.participantid WHERE dp.districtid = $id

The above gives me a syntax error.


DELETE participantevent FROM participantevent pe INNER JOIN district_participants dp on dp.id = pe.participantid WHERE dp.districtid = $id

The above tells me the participantevent table doesn’t exist, but it does

What do you guys think?

2

You need to use the alias you gave the table:


DELETE
  pe
FROM
  participantevent pe
    INNER JOIN
  district_participants dp
    ON
  dp.id = pe.participantid
WHERE
  dp.districtid = $id

:slight_smile:

3

hahaha thanks… I figured it out right after I posted

Cheers