469,621 Members | 1,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

can't execute delete with the IN operator

Hello to everyone,
I am an experienced user in MSSQL Server.
Lately I have been started using MySQL. I managed to create my
database and tables.

When I wanted to execute the following query:
delete
from adminpages
where parentid IN ( select DISTINCT A.id from adminpages AS A where
A.name='galeries' );

I have received the following error message:
Error Code : 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 'select DISTINCT A.id from adminpages AS A where
A.name='galeries'
(0 ms taken)
Can someone tell me what's wrong?
Jul 20 '05 #1
4 2023
Yossi Naggar wrote:
Hello to everyone,
I am an experienced user in MSSQL Server.
Lately I have been started using MySQL. I managed to create my
database and tables.

When I wanted to execute the following query:
delete
from adminpages
where parentid IN ( select DISTINCT A.id from adminpages AS A where
A.name='galeries' );

I have received the following error message:
Error Code : 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 'select DISTINCT A.id from adminpages AS A where
A.name='galeries'
(0 ms taken)
Can someone tell me what's wrong?


Subselects are not supported in MySQL 4.0.x which is why your query is
failing. They will become available in MySQL 4.1.x

In the meantime, you can often rewrite subselect queries using joins. I've
done this with MySQL before, but never when joining a table to itself.

The following *should* work for your query above, although I take no
responsibility if it deletes more data than expected :) In the example I
created on my site a while back, I used the tablenames in the delete part
of the query but you wouldn't be able to do this as it would probably
delete both sets of records (ie the parent as well a child records). I am
not sure if using the alias name will actually work and don't have time to
test it.

DELETE ap1.*
FROM adminpages ap1
INNER JOIN adminpages ap2
ON ap1.parent_id = ap2.id
WHERE ap2.name = 'galeries'

The article on my site with other examples is at
http://www.electrictoolbox.com/artic...-table-delete/

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #2
Yossi Naggar wrote:
Can someone tell me what's wrong?


Subqueries are not implemented in MySQL until version 4.1.

Try the MySQL syntax for joins in DELETE statements.
See this page for details:
http://dev.mysql.com/doc/mysql/en/DELETE.html

Regards,
Bill K.
Jul 20 '05 #3
Bill Karwin <bi**@karwin.com> wrote in message news:<cl********@enews3.newsguy.com>...
Yossi Naggar wrote:
Can someone tell me what's wrong?


Subqueries are not implemented in MySQL until version 4.1.

Try the MySQL syntax for joins in DELETE statements.
See this page for details:
http://dev.mysql.com/doc/mysql/en/DELETE.html

Regards,
Bill K.


Well thank you for the information ....
However, the combined joins in the delete query don't serve the
purpose so good ...

By the way, I don't fully understand what's the point of not
implementing subqueries and other functionalities like: stored
procedures ...
Jul 20 '05 #4
Yossi Naggar wrote:
However, the combined joins in the delete query don't serve the
purpose so good ...
My sincere apologies! I did not read your stated problem closely
enough. As I read the docs more carefully, I realize that in MySQL 4.0,
a cross-table delete can't use table aliases, so this makes it
impossible to delete only the parent row when doing a self-join like the
following:

DELETE a1
FROM adminpages a1 INNER JOIN adminpages a2 ON (a1.parentid = a2.id)
WHERE a2.name = 'galeries';

The above only works in MySQL 4.1. But if you were running MySQL 4.1,
your first try with the subqueries would work anyway.

I think you can't do this in one query. You must do it in two steps:

1. Fetch into an application variable results of this query:
SELECT DISTINCT A.id FROM adminpages AS A WHERE A.name='galeries'
2. Concatenate the results together in your application code, to form
one comma-separated string.
3. Then provide that string to the delete statement:
DELETE FROM adminpages WHERE parentid IN ( $results );

Another option is to use a temp table, but this also requires multiple
steps:

1. Create temp table with one column to store the ID's
CREATE TEMPORARY TABLE tempIDs (id INTEGER);
2. Fetch the list of ID's you want, and store them in the temp table:
INSERT INTO tempIDs SELECT DISTINCT A.id FROM adminpages AS A WHERE
A.name='galeries'
3. Use a multi-table delete:
DELETE adminpages, tempIDs
FROM adminpages INNER JOIN tempIDs ON (adminpages.parentid = tempIDs.id)
By the way, I don't fully understand what's the point of not
implementing subqueries and other functionalities like: stored
procedures ...


No RDBMS on earth implements the full ANSI/ISO SQL specification. I
read an article once that estimated it would take 1000 engineer-years to
implement SQL in its entirety. Most users and companies aren't able to
wait that long. They have to make some judgement about which subset of
features will be useful to a reasonable number of users, release their
product with those features, and then leave the rest to work on later.

Regards,
Bill K.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dave | last post: by
2 posts views Thread by skscpp | last post: by
5 posts views Thread by | last post: by
1 post views Thread by Senthilvel Samatharman | last post: by
5 posts views Thread by Tom Smith | last post: by
4 posts views Thread by mail.dsp | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.