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 datas

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

Answer

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;