469,643 Members | 1,238 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

DELETE and LEFT JOIN problem

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.
Jul 19 '05 #1
2 16409
BDR
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.


Jul 19 '05 #2
BDR
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.


Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Marek Lewczuk | last post: by
reply views Thread by Oliver Spiesshofer | last post: by
5 posts views Thread by Marek Kotowski | last post: by
2 posts views Thread by Bruce Duncan | last post: by
2 posts views Thread by R. Tarazi | last post: by
2 posts views Thread by Ryan | last post: by
1 post views Thread by Chris Thompson | last post: by
9 posts views Thread by Alan Lane | last post: by
!NoItAll
4 posts views Thread by !NoItAll | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.