http://www.mysql.com/doc/en/DELETE.html
From this, one can quickly deduce that you cannot use a join with a
delete. (Joins are for selecting).
If you want a multi-table delete, then read into the documentation and
you'll find that this wasn't implemented until version 4.x.
From
http://www.mysql.com/doc/en/DELETE.html:
Quote-
The first multi-table delete format is supported starting from MySQL
4.0.0. The second multi-table delete format is supported starting from
MySQL 4.0.2.
The idea is that only matching rows from the tables listed before the
FROM or before the USING clause are deleted. The effect is that you can
delete rows from many tables at the same time and also have additional
tables that are used for searching.
The .* after the table names is there just to be compatible with Access:
DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
or
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
In the above case we delete matching rows just from tables t1 and t2.
If an ORDER BY clause is used (available from MySQL 4.0.0), the rows
will be deleted in that order. This is really only useful in conjunction
with LIMIT. For example:
DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1
- End Quote
So, the solution to your problem is to run two queries:
1. SELECT GroupID FROM Groups LEFT JOIN Users ON UsersID = UserID
WHERE UsersID IS NULL;
Save GroupID and pass it on to the next one(s):
2. Delete from Groups where etc...
Cheers.
michael wrote:
Gotta post because this is driving me nuts.
Trying to DELETE orphans. I can successfully:
SELECT GroupID FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE
UsersID IS NULL;
but when I try:
DELETE FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE UsersID
IS NULL;
I get:
ERROR 1064: You have an error in your SQL syntax near 'LEFT JOIN Users
ON UsersID = UserID WHERE UsersID IS NULL' at line 1
I can also successfully:
DELETE FROM Groups WHERE GroupID = '<specific>';
I have been trying several different methods, but a little hesitant to
go to far, considering I'm DELETEing. I have been checking out past
postings, but nothing that fits this situation.
Any help on this sql would be greatly appreciated. I'm using mysql
version 3.23.55.
Thanks.
