Geek Answers Handbook

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

I want to remove 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 does not work:

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

MySQL produces the following error:

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

What am I doing wrong here?

+4
source share
6 answers

MySQL 4 and above support deletion 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 use MySQL below version 4, you need to delete rows from one table at a time, and you can use one of the other solutions posted here.

+10
source

to try

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

I deleted google SQL code from two tables at once and found this forum post

+2
source

MySQL will allow you to remove the connection, but you must specify which columns, so using your example, the correct syntax will be

  DELETE sheets. *, Entries. * FROM sheets, entries WHERE entries.sheetID = sheets.id AND sheets.clientID = 13
+2
source

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 remove from sheets and

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

if its from the records

0
source

I believe that 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
0
source

You can delete only one table at a time. If you want to use 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);
0
source

More articles:


All Articles