mysql - LIKE 子句和 AND 运算符在 DELETE 期间不起作用

Question

我正在尝试在 mysql 中执行这样的命令:

DELETE FROM product_description, product, product_to_store, url_alias WHERE
product_description.name LIKE '%|%' AND product.product_id = 
product_description.product_id AND product_to_store.product_id = 
product_description.product_id AND url_alias.query = 
CONCAT('product_id=',product_description.product_id)

但是它给了我一个错误:

#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 'WHERE product_description.name LIKE '%|%' AND product.product_id = product_descr' at line 1

当我将“DELETE”替换为“SELECT *”时，查询就像一个魅力。

根据mysql文档

Currently, you cannot delete from a table and select from the same table in a subquery.

所以现在我不知道如何在不使用 PHP 的情况下执行此类查询

已编辑

我的最终查询有效:

DELETE product_description, product, product_to_store, url_alias 
FROM product_description, product, product_to_store, url_alias 
  WHERE product_description.name LIKE '%|%' AND product.product_id = 
  product_description.product_id AND product_to_store.product_id = 
  product_description.product_id AND url_alias.query = 
  CONCAT('product_id=',product_description.product_id)

所以提示是在 DELETE 之后添加所有 4 个表名以使其工作，如 JW 所写

Answer 1

您需要指定删除发生的时间，在下面的示例中，表 product_description 中的记录将被删除

DELETE product_description
FROM product_description, product, product_to_store, url_alias 
WHERE product_description.name LIKE '%|%' AND product.product_id = 
      product_description.product_id AND product_to_store.product_id = 
      product_description.product_id AND url_alias.query = 
      CONCAT('product_id=',product_description.product_id)

或者使用ANSI SQL-92格式

DELETE  product_description
FROM    product_description
        INNER JOIN product 
            ON product.product_id = product_description.product_id
        INNER JOIN product_to_store 
            ON product_to_store.product_id = product_description.product_id
        INNER JOIN url_alias 
            ON url_alias.query = CONCAT('product_id=',product_description.product_id)
WHERE   product_description.name LIKE '%|%'