Delete with select in mysql

Question

I have 3 columns(id,parent,value) with some data and this query:

SELECT * FROM test WHERE id = (
    SELECT parent FROM test WHERE id=2 AND value='value' LIMIT 1
);

The query above works great.

Now how can I delete instead of select that id in one query?

Answer 1

You cannot delete from a table and select from that same table in a subselect.

You can however use that table in a self-join.

DELETE t1 FROM test t1
INNER JOIN test t2 ON (t1.id = t2.parent)
WHERE t2.id = 2 and t2.value = 'value'

You cannot use limit not order by in a joined delete statement.
It's either the join or the limit/order by, take your pick.

See: http://dev.mysql.com/doc/refman/5.5/en/delete.html

Answer 2

DELETE FROM test WHERE id = ( select * from (
    SELECT parent FROM test WHERE id=2 AND value='value' LIMIT 1 ) as t
)