As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

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 'AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM ter' at line 1

I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what's wrong here?

Update: As answered below, MySQL does not allow the table you're deleting from be used in a subquery for the condition.

Took some time but I got it to work. Important: 1) The first table must be aliased as shown here with "e", 2) the "x" at the end is not a placeholder, it is the alias for the temp table produced by the subquery "(SELECT id FROM tableE WHERE arg = 1 AND foo = 'bar')".
– Tilman HausherrMar 8 '13 at 11:28

3

Why does this work? This changes a lot for me, but moreover, it shouldn't work. It does work, but it shouldn't.
– donatJApr 14 '14 at 21:53

1

unbelievable. this actually works! but you are not forced to alias the table with e... you can use any alias you want.
– Andrei SandulescuApr 29 '14 at 13:37

@jakabadambalazs: We can't use the same table (e) in a DELETE and in its sub-SELECT. We can, however use a sub-sub-SELECT to create a temporary table (x), and use that for the sub-SELECT.
– Steve AlmondOct 6 '14 at 9:41

is not about alias, please check the OP again
– ajrealDec 17 '10 at 14:24

@ajreal - I did, and please notice the error begins at the alias definition, and MySQL documentation explicitly states you need to use the alias in the DELETE statement as well as the FROM clause. Thanks for the downvote, though.
– JNKDec 17 '10 at 14:25

simply do this delete from your_table as t1 where t1.id in(select t2.id from your_table t2); what did you get ?
– ajrealDec 17 '10 at 14:27

I needed to remove secure_links from my table that referenced the conditions table where there were no longer any condition rows left. A housekeeping script basically. This gave me the error - You cannot specify target table for delete.

So looking here for inspiration I came up with the below query and it works just fine.
This is because it creates a temporary table sl1 that is used as the reference for the DELETE.

DELETE FROM `secure_links` WHERE `secure_links`.`link_id` IN
(
SELECT
`sl1`.`link_id`
FROM
(
SELECT
`sl2`.`link_id`
FROM
`secure_links` AS `sl2`
LEFT JOIN `conditions` ON `conditions`.`job` = `sl2`.`job`
WHERE
`sl2`.`action` = 'something' AND
`conditions`.`ref` IS NULL
) AS `sl1`
)

Isn't the "in" clause in the delete ... where, extremely inefficient, if there are going to be a large number of values returned from the subquery? Not sure why you would not just inner (or right) join back against the original table from the subquery on the ID to delete, rather than us the "in (subquery)".?

DELETE T FROM Target AS T
RIGHT JOIN (full subquery already listed for the in() clause in answers above) ` AS TT ON (TT.ID = T.ID)

And maybe it is answered in the "MySQL doesn't allow it", however, it is working fine for me PROVIDED I make sure to fully clarify what to delete (DELETE T FROM Target AS T). Delete with Join in MySQL clarifies the DELETE / JOIN issue.