delete where exists ???

[Rotsj]

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

[Aggro]

Re: delete where exists ???

Rotsj wrote:
[color=blue]
> unfortunately, i get error 1064 'check your syntax'. Is it not possible to[/color]

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

http://dev.mysql.com/doc/mysql/en/delete.html

[Bill Karwin]

Re: delete where exists ???

Rotsj wrote:[color=blue]
> 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?[/color]

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.