Delete records from table A where field x in Table B = y

Question

I have the following query I have been using and it works well enough for displaying the records I want to delete in phpMyAdmin:

SELECT zen_customers_basket.*
FROM zen_customers_basket, zen_products
WHERE zen_customers_basket.products_id = zen_products.products_id
            AND zen_products.products_availability = 'out of stock'

I then check all the records and then delete them. It's fairly quick and painless, but I need a query that will automatically delete these records so I can automate it with some other queries that are scheduled to run once per hour. Any and all help would be appreciated. I know how to delete records but I'm not sure of the proper syntax here since I'm selecting things from two different tables and I don't want to screw anything up.

If you're just looking to delete the records that match the SELECT above, why not just use DELETE from with the same WHERE clause? — RonaldBarzell, Dec 21 '12 at 23:06

score 0 · Answer 1 · answered Dec 21 '12 at 23:11

0

DELETE zen_customers_basket
FROM zen_customers_basket, zen_products
WHERE zen_customers_basket.products_id =          zen_products.products_id
        AND zen_products.products_availability =    'out of stock'

answered Dec 21 '12 at 23:11

Greg Oks

2,700
4
35
41

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

    DELETE FROM Table  WHERE EXISTS (SELECT zen_customers_basket.*
FROM zen_customers_basket, zen_products
WHERE zen_customers_basket.products_id = zen_products.products_id
            AND zen_products.products_availability = 'out of stock')

https://stackoverflow.com/questions/8019957/delete-from-where-in

edited May 23 '17 at 12:14

Community

1
1

answered Dec 21 '12 at 23:11

CodeMonkey

1,087
2
15
24

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

Delete using same conditions as per your query. Not sure the deleting based on hour. Please clarify.

DELETE FROM zen_customers_basket
   INNER JOIN zen_products
   ON zen_customers_basket.products_id = zen_products.products_id
   WHERE zen_products.products_availability = 'out of stock'

Reference: T-SQL: Selecting rows to delete via joins

edited May 23 '17 at 10:26

Community

1
1

answered Dec 21 '12 at 23:13

bonCodigo

14,268
1
48
91

Delete records from table A where field x in Table B = y

3 Answers3