SELECT (with LEFT JOIN) sql query, to DELETE sql query

Question

This query SELECT sql that I wrote, I'd need to actually delete all the data it provides in output, just that I can not turn it properly in a DELETE sql query without running into a syntax error.

Here the query:

SELECT *
FROM ps_attribute_impact AS at
LEFT JOIN
    ps_product_attribute AS prod_at
ON    at.id_product = prod_at.id_product
LEFT JOIN
    ps_product_attribute_combination AS comb
ON    prod_at.id_product_attribute = comb.id_product_attribute
WHERE comb.id_attribute='1' AND at.price='0.00' AND at.id_attribute='1' AND prod_at.price='0.00'

I've tried this 3 queries:

DELETE *
FROM ps_attribute_impact AS at
LEFT JOIN
    ps_product_attribute AS prod_at
ON    at.id_product = prod_at.id_product
LEFT JOIN
    ps_product_attribute_combination AS comb
ON    prod_at.id_product_attribute = comb.id_product_attribute
WHERE comb.id_attribute='1' AND at.price='0.00' AND at.id_attribute='1' AND prod_at.price='0.00'

and

DELETE ps_attribute_impact.*
FROM ps_attribute_impact AS at
LEFT JOIN
    ps_product_attribute AS prod_at
ON    at.id_product = prod_at.id_product
LEFT JOIN
    ps_product_attribute_combination AS comb
ON    prod_at.id_product_attribute = comb.id_product_attribute
WHERE comb.id_attribute='1' AND at.price='0.00' AND at.id_attribute='1' AND prod_at.price='0.00'

and

DELETE
FROM ps_attribute_impact AS at
LEFT JOIN
    ps_product_attribute AS prod_at
ON    at.id_product = prod_at.id_product
LEFT JOIN
    ps_product_attribute_combination AS comb
ON    prod_at.id_product_attribute = comb.id_product_attribute
WHERE comb.id_attribute='1' AND at.price='0.00' AND at.id_attribute='1' AND prod_at.price='0.00'

But I have not had any success. Can anyone help me?

See this SO question: http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql — Nate C-K, Jan 16 '14 at 16:59
In particular, this answer: http://stackoverflow.com/a/11442104/219155 — Nate C-K, Jan 16 '14 at 17:01
The problem is that I want to delete the results from all the tables "called in question" by JOIN, not only from the table ps_attribute_impact, can I do this? — user2806879, Jan 16 '14 at 17:08
MySQL does allow you to specify more than one table after the DELETE keyword: http://dev.mysql.com/doc/refman/5.0/en/delete.html However, when trying to delete more than one table at once, you can end up with a big mess if you have foreign key dependencies between your tables. — Nate C-K, Jan 16 '14 at 18:21

score 0 · Answer 1 · answered Jan 16 '14 at 17:02

The correct syntax for multi table delete is like this

DELETE ps_attribute_impact 
  FROM ps_attribute_impact AS at 
  LEFT JOIN ps_product_attribute AS prod_at 
    ON at.id_product = prod_at.id_product 
  LEFT JOIN ps_product_attribute_combination AS comb 
    ON prod_at.id_product_attribute = comb.id_product_attribute 
 WHERE comb.id_attribute='1' 
   AND at.price='0.00' 
   AND at.id_attribute='1' 
   AND prod_at.price='0.00'

score 0 · Answer 2 · answered Jan 16 '14 at 17:02

I'm not sure about the logic of the query, but the syntax for a join in a delete is:

DELETE at 
    FROM ps_attribute_impact at LEFT JOIN
         ps_product_attribute prod_at
         ON at.id_product = prod_at.id_product LEFT JOIN
         ps_product_attribute_combination comb
         ON prod_at.id_product_attribute = comb.id_product_attribute
    WHERE comb.id_attribute='1'  AND at.price = '0.00' AND at.id_attribute = '1' AND
          prod_at.price = '0.00';

Because you have comparisons on all the tables in the where clause, the left joins are really doing inner join. For clarity, you should change them to inner join.

SELECT (with LEFT JOIN) sql query, to DELETE sql query

2 Answers2