Thanks Brent, good tip. Works like a charm.
On Jun 14, 2007, at 7:42 PM, Brent Baisley wrote:
Here's a little trick. Get your DELETE query working as a SELECT.
Then replace everything before FROM with DELETE tablename.
SELECT order_items.ord_id FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1
...becomes...
DELETE order_items FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1
You may have to tweak it a little, but the DELETE tablename FROM
option is something many people miss. Although I'm pretty sure it's
covered in the manual.
On 6/14/07, Ben Liu <[EMAIL PROTECTED]> wrote: I'm trying to
delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run
a series
of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
item_id
ord_id
orders (order details such as person's name, address, phone number,
etc)
ord_id
cust_id
I have set all "shopping carts" or initiated/incomplete orders so that
orders.cust_id=-1
I wish to "clear all shopping carts" so I need to delete two sets
of items:
1) All records in order_items where
order_items.ord_id=orders.ord_id and
orders.cust_id=-1
2) All records in orders where orders.cust_id=-1
I can do #2 easily enough, but I can't figure out how to write the
delete
query for #1.
I've tried:
DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1
DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1
Neither of them seem to work.
Thanks for any help.
~Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
