Fun with MySQL - How to write a delete statement with an IN

Question

When I try

DELETE FROM `TreePaths` WHERE `descendant` IN (SELECT `descendant` FROM `TreePaths` WHERE `ancestor`= 0x04);

I get

#1093 - You can't specify target table 'TreePaths' for update in FROM clause

How can I get the delete to work?

update: Table structure:

CREATE TABLE TreePaths (
    ancestor        VARBINARY(16) NOT NULL,
    descendant      VARBINARY(16) NOT NULL,
    PRIMARY KEY (`ancestor`, `descendant`),
    FOREIGN KEY (`ancestor`) REFERENCES Library(iD),
    FOREIGN KEY (`descendant`) REFERENCES Library(iD)
);

Table data:

ancestor    descendant
        01  01
        02  02
        02  03
        03  03
        02  04
        04  04
        02  05
        04  05
        05  05
        02  06
        04  06
        06  06
        07  07
        08  08
        09  09
        10  10

I guess its the same issue http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause — Zohaib, Nov 19 '11 at 02:21
read this one : http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html — xkeshav, Nov 19 '11 at 02:23
possible duplicate of [MySQL DELETE FROM with subquery as condition](http://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition) — ajreal, Nov 19 '11 at 02:26

score 3 · Accepted Answer · answered Nov 19 '11 at 02:31

3

In MySQL it's easier to do a multi-table delete:

DELETE paths_to_subtree FROM `TreePaths` AS paths_to_subtree
JOIN `TreePaths` AS subtree USING (`descendant`)
WHERE subtree.`ancestor`= 0x04;

answered Nov 19 '11 at 02:31

Bill Karwin

538,548
86
673
828

Thanks a ton. MySQL seems to make everything just a little harder than it needs to be :-/ – Justin808 Nov 19 '11 at 02:36
Also thanks a ton for http://www.slideshare.net/billkarwin/models-for-hierarchical-data – Justin808 Nov 19 '11 at 02:37

score 0 · Answer 2 · answered Nov 19 '11 at 02:35

You could create a temporary table to store the descendants:

CREATE TEMPORARY TABLE tmpTable (descendants VARBINARY(16) NOT NULL);
INSERT INTO tmpTable(descendants) (SELECT descendant FROM TreePaths WHERE ancestor = 0x04);
DELETE FROM TreePaths WHERE descendant IN (SELECT descendant from tmpTable);
DROP TABLE tmpTable;

The last line is optional and can be used if you want to free up memory before making something else - temp tables are droped at the end of the session

Fun with MySQL - How to write a delete statement with an IN

2 Answers2