Delete with joining to the same table
it is possible to execute a delete query statement that joins the same table, I tried various joins (inner, left) but no luck mysql returns an error
an example of what i need:
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'
+1
source to share
1 answer
While the manual seems to suggest that the INNER JOIN syntax should work in DELETE, I know that this alternative with the join clause has been moved to where the condition will 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
Edit: 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
+3
source to share