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.
>> Stay informed about: delete where exists ??? 
FAQ
Search
Profile
Private Messages
Log in
