How to turn this MySQL SELECT query into a DELETE query?

Question

I want to delete certain items from the database. I have the following query:

SELECT * 
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

This works, and returns 2 results.

Now I want to turn this SELECT query into a DELETE query. However, the following doesn't work:

DELETE FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

MySQL throws the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE entries.sheetID = sheets.id AND sheets.clientID = 13' at line 1

What am I doing wrong here?

Answer 1

Can you try something like this

DELETE FROM sheets
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

if you want to delete from sheets and

DELETE FROM entries
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

if its from entries

Answer 2

I believe you can only DELETE from one table at a time.

DELETE FROM entries
WHERE entries.sheetID IN
(SELECT ID FROM sheets WHERE clientID = 13)

DELETE FROM sheets
WHERE sheets.clientID = 13

Answer 3

try

DELETE sheets, entries 
FROM sheets, entries
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

I googled SQL delete from 2 tables at once and found this forum post

Answer 4

MySQL 4 and up supports deleting from multiple tables at once, using the following syntax:

DELETE sheets, entries
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13

If you're using MySQL below version 4, then you need to delete rows from one table at a time, and you can use one of the other solutions posted here.

Answer 5

You can only delete from one table at a time. If you want to drive both deletes from the same query, you can do something like the following:

DELETE from sheets where id in (
SELECT sheets.id
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13);
DELETE from entries where id in (
SELECT entries.id
FROM sheets, entries 
WHERE entries.sheetID = sheets.id AND sheets.clientID = 13);

Answer 6

MySQL will let you delete over a join, but you must specify which columns, so using your example, the correct syntax would be

DELETE sheets.*, entries.* FROM sheets, entries WHERE entries.sheetID = sheets.id AND sheets.clientID = 13