Re: need help with delete query

[Ben Liu]

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]

need help with delete query

[Ben Liu]

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

Re: Find ids that are NOT in other two tables.

[Ben Liu]

Uh, I don't have anything to add to this thread except that this was
the *exact* problem I was trying to solve and this really helped me a
lot. Thanks guys.

-BL

On 5/22/06, Jay Pipes <[EMAIL PROTECTED]> wrote:

Yesmin Patwary wrote:
>   Hi,
>
>   Greatly appreciated your kind help.  Would it work, if I rewrite your query 
as below to remove all records from company_db that don't have any relation to 
other two tables?
>
>
> DELETE
> FROM company_db c


...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]