The Nice Spider wrote:
> Hi,
>
> This query running fine on 4.0.25 but when trying on
> 3.23 an error occurs.
> can one help me to find correct command for 3.23?
>
> DELETE FROM A
> USING A
> RIGHT JOIN B ON B.id = A.sectionid
> WHERE B.id is null
>
> This task is deleted any row in A that have no items
> on B.
Then that should be a LEFT JOIN, not a RIGHT.
sheeri kritzer wrote:
You are not being honest with us on the list.....
Firstly, the error you got:
You have an error in your SQL syntax near 'USING USING
A
RIGHT JOIN B ON B.id = A.sectionid' at line 1
SQL=DELETE FROM A USING A
RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null
indicates that you used the USING keyword twice in your query, which
won't work in any version of MySQL.
I noticed this, too. As I doubt his tables are named A and B, the join seems
the reverse of his intention, and he says the query works in 4.0.25, I expect
this is a pasting/editing error. Of course, that's what happens when one
simplifies instead of posting the real query. In any case, the issue described
is actually expected behavior.
I tried to replicate what you have:
<snip>
mysql> select * from A USING A RIGHT JOIN B ON B.id=A.sectionid WHERE
B.id is null;
ERROR 1064 (42000): 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 'USING A RIGHT JOIN B ON B.id=A.sectionid WHERE
B.id is null' at line 1
As you can see, on MySQL 4.1.12 I'm getting an error. ( I used select
* from instead of delete from because that's how I test out delete
queries to make sure I don't do something dumb).
That's a good idea, but "FROM A USING A RIGHT JOIN B" is valid syntax for
DELETEs, not FOR SELECTs.
I think you don't want the "USING A" at all:
select * from A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null;
works just fine for me.
Yes, that's the corresponding SELECT statement, but he is trying to DELETE rows.
And it's true that in 3.23 you could not do a multiple-table UPDATE
(that was introduced in 4.0.0). So I'm guessing that's what you
really meant.
That's the issue. The query
DELETE FROM A
USING A LEFT JOIN B ON B.id = A.sectionid
WHERE B.id is NULL;
is a perfectly valid multi-table DELETE statement
<http://dev.mysql.com/doc/refman/4.1/en/delete.html>. The problem is that
multi-table DELETE was added in 4.0. Hence the syntax error he gets in 3.23.
Why are you even bothering, though? why not just use
DELETE FROM A WHERE sectionid IS NULL;
DELETE FROM B WHERE id IS NULL;
? Because that's all you're really doing in those queries.
No, that's not equivalent at all. First, he doesn't want to delete any rows
from B. He only wants to delete rows in A that don't have corresponding rows in
B. Second, the rows in A to be deleted have non-NULL sectionids. The
sectionids just don't have corresponding rows in B.
I believe the answer is that there isn't a way to fix the syntax for 3.23,
because 3.23 simply doesn't support multi-table deletes. An alternative
solution is needed.
One option would be to do this programmatically. Collect the sectionids with a
SELECT, then delete them with a separate query.
Another option would be to select the good rows into a new table, then use this
new table to recreate table A. Something like
CREATE TABLE A2
SELECT A.*
FROM A JOIN B ON A.sectionid = B.id;
then
DELETE FROM A;
INSERT INTO A SELECT * FROM A2;
or
RENAME TABLE A TO A_old, A2 TO A;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
