mysql multiple table DELETE rows syntax error

Question

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

Can you post your error?

Sadikhasan
– Sadikhasan

2014-07-22 09:17:14 +00:00
Commented Jul 22, 2014 at 9:17 — Sadikhasan
– Sadikhasan, Commented Jul 22, 2014 at 9:17
What error you are recieveing??

Ankit Bajpai
– Ankit Bajpai

2014-07-22 09:17:26 +00:00
Commented Jul 22, 2014 at 9:17 — Ankit Bajpai
– Ankit Bajpai, Commented Jul 22, 2014 at 9:17

Community · Accepted Answer · 2014-08-08 13:40:04Z

2

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;

edited Aug 8, 2014 at 13:40

CommunityBot

11 silver badge

answered Jul 22, 2014 at 9:18

Swapnil

8,3665 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Winiecki Over a year ago

Are there cases where the second form (with USING) does not work, or do you just prefer the first form (that does not include USING)?

David Winiecki Over a year ago

For LEFT JOIN that is.

Gareth Williams · Accepted Answer · 2014-07-22 09:19:51Z

1

Your JOIN should have an ON part

answered Jul 22, 2014 at 9:19

Gareth Williams

6076 silver badges4 bronze badges

Comments

Ankit Bajpai · Accepted Answer · 2014-07-22 09:28:34Z

1

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

answered Jul 22, 2014 at 9:28

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Comments

Matoeil · Accepted Answer · 2014-07-22 09:35:45Z

0

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_%'

answered Jul 22, 2014 at 9:35

Matoeil

7,36711 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

mysql multiple table DELETE rows syntax error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related