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?
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.
t1 and t2 to differentiate between the table from which he is deleting and the table to which he is joining.DELETE FROM test WHERE id = ( select * from (
SELECT parent FROM test WHERE id=2 AND value='value' LIMIT 1 ) as t
)
