Search code examples
mysqlsqlmysql-error-1064sql-delete

How does mysql delete syntax differ from select?

This:

SELECT * 
  FROM tbl_playlists, tbl_playlistsongs 
 WHERE tbl_playlists.playlist_id = tbl_playlistsongs.playlist_id 
   AND tbl_playlists.playlist_id = 1

...works no problem. But:

DELETE from tbl_playlists, tbl_playlistsongs 
 WHERE tbl_playlists.playlist_id = tbl_playlistsongs.playlist_id 
   AND tbl_playlists.playlist_id = 1

...says I have a syntax error. They're identical other than the SELECT * vs DELETE. It still makes perfect logical sense to me.. but I must be missing something!

Solution

  • Traditional SQL doesn't support multi-table deletions, but MySQL does. That means you're using MySQL specific syntax:

    DELETE pl, pls
  FROM TBL_PLAYLISTS pl
  JOIN TBL_PLAYLISTSONGS pls ON pls.playlist_id = pl.playlist_id
 WHERE pl.playlist_id = 1

    Reference: