Mysql advanced query

Question

I am getting an error:

ERROR 1093 (HY000): You can't specify target table 'postac' for update in FROM clause

on query:

UPDATE postac
SET statek= (
    SELECT statek
    FROM postac
    WHERE nazwa='Bjorn')
WHERE nazwa='*a*';

Can someone fix this ?

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) — André Laszlo, Apr 14 '16 at 10:39
This behaviour is explained in the [documentation of the `UPDATE` statement](http://dev.mysql.com/doc/refman/5.7/en/update.html): *"You cannot update a table and select from the same table in a subquery."* — axiac, Apr 14 '16 at 10:54
What if the subquery returns more than one row? The logic of the query is wrong in this case. — axiac, Apr 14 '16 at 10:58

score 1 · Answer 1 · answered Apr 14 '16 at 10:39

1

Try like this:

update postac p1
inner join postac p2 on p1.id = p2.id and p2.nazwa='Bjorn'
set p1.statek = p2.statek
where p1.nazwa='*a*'

answered Apr 14 '16 at 10:39

Rahul Tripathi

Assuming `id` is the `PK` of the table, your query is a `no-op`. – axiac Apr 14 '16 at 10:57

1 Answers1