我试图在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文档所以现在我不知道如何执行这样的查询,不使用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所写的
您需要指定应该在哪个位置进行删除,在下面的示例中,将删除表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 '%|%'
删除语法为:
DELETE FROM table_name [WHERE Clause]
FROM子句中不能有多个table_name(没有JOIN和USING子句)。
DELETE table_name FROM table_name
INNER JOIN ....
或
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
参考: 13.2.2。删除语法
JW先生已经给你答案了
使用示例
DELETE
FROM `product_description`,
`product`,
`product_to_store`,
`url_alias`
USING `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 '%|%'
旁注:对于用户定义的标识符总是使用(')。