Delete post with smallest id SQL

Question

Delete from post 
where id_post 
in 
( 
    select MIN(id_post)
    from post
    where id_owner='2'
)

Returns: "You can't specify target table 'post' for update in FROM clause"

What am I doing wrong?

Answer 1

The problem is that MySQL, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

The solution is to replace the instance of post in the sub-query with (select MIN(id_post) from post where id_owner='2' ), like this

Delete from post 
where id_post 
in 
( 
    select id_post 
    from (select MIN(id_post)
    from post
    where id_owner='2') as A
)