加入同一张表删除 [英] delete with joining the same table

问题描述

是否可以执行连接同一表的删除查询语句， 我尝试了各种联接(内部，左侧)，但是没有运气的mysql返回错误

is it posssible to execute a delete query statement that joins the same table, i've tried various joins (inner, left) but no luck mysql returns error

我需要的示例:

DELETE `a` FROM `t1` AS `a`
INNER JOIN `t1` AS `b` USING `some_field_b`
WHERE 
    `a`.`some_field_a` = 'value_x' AND 
    `b`.`some_field_a` = 'value_y'

推荐答案

尽管

Although the manual seems to suggest the INNER JOIN syntax should work in a DELETE, I know that this alternative with the join clause moved to the where condition would work....

DELETE  a.* FROM t1 AS a, t1 as b 
WHERE 
    a.some_field_b=b.some_field_b AND
    a.some_field_a = value_x AND 
    b.some_field_a = value_y

我刚刚尝试过，这对我有用:

I just tried this, which worked for me:

DELETE a FROM t1 AS a 
INNER JOIN t1 as b USING(some_field_b) 
WHERE 
    a.some_field_a = value_x AND 
    b.some_field_a = value_y

这篇关于加入同一张表删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！