Deleting a row of data using inner joins

Question

I am trying to delete a row of data from my table using Inner Joins however my code doesn't seem to be working. I firstly wrote out a Select statement to ensure I was selecting the correct data:

SELECT * FROM tblPlaylist_Tracks
INNER JOIN tblPlaylist ON tblPlaylist_Tracks.PlaylistID = tblPlaylist.PlaylistID
INNER JOIN tblTrack ON tblPlaylist_Tracks.TrackID = tblTrack.TrackID
WHERE Playlist_Name =  "x" AND Track_Name =  "y";

I then replaced the select with a delete statement however this kicked up an error on line 2:

DELETE FROM tblPlaylist_Tracks
INNER JOIN tblPlaylist ON tblPlaylist_Tracks.PlaylistID = tblPlaylist.PlaylistID
INNER JOIN tblTrack ON tblPlaylist_Tracks.TrackID = tblTrack.TrackID
WHERE Playlist_Name =  "x" AND Track_Name =  "y";

I have attempted to specify what I want to delete between the Delete From statement however this doesn't seem to work as the query no longer recognizes DELETE as a command Appreciate any help possible. Many Thanks

any reason you don't show us the error message? – Hogan Apr 24 '18 at 20:27 — Hogan, Apr 24 '18 at 20:27

score 1 · Answer 1 · answered Apr 24 '18 at 19:46

1

That makes sense - SQL would interpret your request as one to delete from three tables, or from a "virtual" table built from your joins.

What I would try is wrapping your working select into the delete:

delete from tblPlaylist_Tracks 
where primaryKey not in (
  SELECT primaryKey FROM tblPlaylist_Tracks
  INNER JOIN tblPlaylist ON tblPlaylist_Tracks.PlaylistID = 
  tblPlaylist.PlaylistID
  INNER JOIN tblTrack ON tblPlaylist_Tracks.TrackID = tblTrack.TrackID
  WHERE Playlist_Name =  "x" AND Track_Name =  "y";
)

and "primaryKey" would, of course, be the distinct identifier for tblPlaylist_Tracks

answered Apr 24 '18 at 19:46

theGleep

1,179
8
14

Thanks this makes sense however i'm receiving this error: "#1093 - You can't specify target table 'tblPlaylist_Tracks' for update in FROM clause" – Steven Smith Apr 24 '18 at 19:52
A little Google-fu and ... https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – theGleep Apr 24 '18 at 19:58

score 0 · Answer 2 · answered Apr 24 '18 at 21:29

I would write this as:

DELETE pt
    FROM tblPlaylist_Tracks pt INNER JOIN 
         tblPlaylist p
         ON pt.PlaylistID = p.PlaylistID INNER JOIN
         tblTrack t
         ON pt.TrackID = t.TrackID
WHERE p.Playlist_Name = 'x' AND t.Track_Name =  'y';

Does this work for you?

I'm not sure what you mean by "no longer recognizes DELETE as a command". When using DELETE with JOIN you need table aliases (or table names) before the FROM.

Deleting a row of data using inner joins

2 Answers2