----- Original Message -----
From: "Ben Liu" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, June 14, 2007 3:11 PM
Subject: need help with delete query
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.
First of all, let me explain that I have been away from database work for
about a year now and I have not kept up with developments in MySQL. However,
I have been working with databases for many years, including many years with
DB2 and a fair bit of work with MySQL until last summer. So, based on that
experience, I'm going to offer you my opinions for what they're worth.
First of all, the first DELETE that you cited should work. I don't see any
reason why it wouldn't except possibly that there are no rows that satisfy
the query. It should be easy to determine if there are any qualifying rows:
simply turn the query into a SELECT and see if any rows satsify the query.
Run:
SELECT * FROM order_items
WHERE order_items.ord_id=orders.ord_id
AND orders.cust_id=-1
If you get no rows from that, then that's why your query failed. In that
case, investigate the UDPATE queries that were supposed to be setting the
cust_id to -1 and see what's wrong with them.
As for the second DELETE, I am dubious that this could ever work but I'm not
100% certain. That query LOOKS like a join and, in DB2 at least, you can
never delete rows from a join. Now, MySQL may tolerate that syntax and not
interpret it as a join so you may want to confirm this with someone familiar
with the version of MySQL you are using.
So, in a nutshell, the first DELETE should work fine but you may not have
the data there that will allow it to do anything.
--
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
