469,568 Members | 1,614 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

delete where exists ???

Hi,

if i run the following query:
select *
from std_order_lines sol
where exists(select * from std_orders so, customers c
where so.customers_id = c.customers_id
and c.test_customer = 'Y'
and so.order_id = sol.order_id)

i get all the order_lines from test_customer 'Y'. I want to delete these
lines so i changed the query to:
delete
from std_order_lines sol
where exists(select * from std_orders so, customers c
where so.customers_id = c.customers_id
and c.test_customer = 'Y'
and so.order_id = sol.order_id)

unfortunately, i get error 1064 'check your syntax'. Is it not possible to
use this construction in a delete?

Rotsj
Jul 23 '05 #1
2 19905
Rotsj wrote:
unfortunately, i get error 1064 'check your syntax'. Is it not possible to


See the syntax and examples in here and modify one for your needs:

http://dev.mysql.com/doc/mysql/en/delete.html
Jul 23 '05 #2
Rotsj wrote:
delete
from std_order_lines sol
where exists(select * from std_orders so, customers c
where so.customers_id = c.customers_id
and c.test_customer = 'Y'
and so.order_id = sol.order_id)

unfortunately, i get error 1064 'check your syntax'. Is it not possible to
use this construction in a delete?


Well, the subquery worked in your SELECT, so you're using MySQL 4.1
which is required for subqueries.

My guess is the use of a table alias in the FROM clause, and then using
that within the subquery.

I find that the following does not give a syntax error (but I didn't
verify that it deletes the correct rows :-).

delete
from std_order_lines
where exists(select * from std_orders so, customers c
where so.customers_id = c.customers_id
and c.test_customer = 'Y'
and so.order_id = std_order_lines.order_id)

Another suggestion would be to do this as a multi-table DELETE, since
EXISTS is equivalent to an inner join:

DELETE sol
FROM std_order_lines AS sol INNER JOIN std_orders AS so
ON sol.order_id = so.order_id
INNER JOIN customers AS c
ON so.customers_id = c.customers_id
WHERE c.test_customer = 'Y';

Regards,
Bill K.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Dave | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
1 post views Thread by Sam | last post: by
3 posts views Thread by NHM | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.