CODEWITHSUNDEEP
mysql
Matoeil
Matoeil

Reputation: 7289

mysql multiple table DELETE rows syntax error

How come this query:

SELECT * 
FROM `store_catalog_product_option` 
    JOIN `store_catalog_product_option_type_value` 
WHERE `product_id`=15676 
    AND `store_catalog_product_option_type_value`.`sku` LIKE '%UNIT_%'

retrieve data.

But replacing 

select *

with 

delete

as such 

DELETE 
FROM `store_catalog_product_option` 
    JOIN `store_catalog_product_option_type_value` 
WHERE `product_id`=15676 
    AND `store_catalog_product_option_type_value`.`sku` LIKE '%UNIT_%'

give syntax error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN store_catalog_product_option_type_value WHERE product_id=15676 AND `sto' at line 1

Upvotes: 0

Views: 908

Answers (4)

Swapnil
Swapnil

Reputation: 8318

For multi-table deletes,

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. 

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

And for LEFT JOIN, you should use something like

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

Upvotes: 2

Matoeil
Matoeil

Reputation: 7289

I needed indeed to use a multiple table syntax, including also a ON condition for the JOIN.

Since i wanted to delete rows in both tables , here is my working query, after your help:

 DELETE O, V FROM `store_catalog_product_option` O JOIN `store_catalog_product_option_type_value` V on O.option_id=V.option_id WHERE `product_id`=15676 AND V.`sku` LIKE '%UNIT_%'

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try somthing like this:-

DELETE FROM `store_catalog_product_option` A
JOIN `store_catalog_product_option_type_value' B
ON A.ID = B.ID
WHERE A.`product_id`=15676 
AND B.`sku` LIKE '%UNIT_%'

Alternatively you can use:-

DELETE A
FROM `store_catalog_product_option` A
JOIN `store_catalog_product_option_type_value' B
ON A.ID = B.ID
WHERE A.`product_id`=15676 
AND B.`sku` LIKE '%UNIT_%'

...to delete only from store_catalog_product_option

Upvotes: 1

Gareth Williams
Gareth Williams

Reputation: 607

Your JOIN should have an ON part

Upvotes: 1

Related Questions