3
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?

Andrea Lacava
  • 33
  • 1
  • 3
  • 4
    Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – Stefano Zanini Mar 16 '17 at 11:04

2 Answers2

3

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
)
Sumit
  • 729
  • 4
  • 9
0

How about using ORDER BY and LIMIT?

Delete p
    from post p
    where id_owner = 2
    order by id_post
    limit 1;

Note: Don't use single quotes around numeric constants. I am guessing id_owner is numeric.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786