Mysql : update query syntax incorrect

Question

I am trying to execute this query but I am getting an error.

update T_CLIENT c set taxe_income = true
where c.id in (select c1.id from T_CLIENT c1, T_ADRESS a
where c1.id = a.client_id and a.country_id = 14  and a.adress_principale is true);

The error is :

You can't specify target table 'c' for update in FROM clause

I don't know how to write this query in order to make it work.
If anyone has an idea...
Thanks

Answer 1

Try like this:

update T_CLIENT c inner join T_ADRESS a on c.id = a.client_id 
set taxe_income = true
where a.country_id = 14  and a.adress_principale is true

Answer 2

You need to do something like this:

UPDATE table SET col = (
  SELECT ... FROM (SELECT.... FROM) AS t);

You cannot update a table and select from the same table in a subquery. as stated here.

Answer 3

You don't need join for this question;

1. way

update T_CLIENT c,T_ADRESS a  set c.taxe_income = true
    where c.id = a.client_id 
    and a.country_id = 14  
    and a.adress_principale is true

2. way

UPDATE T_CLIENT
SET T_CLIENT.taxe_income  = true
    FROM T_CLIENT c,T_ADRESS a
        WHERE c.id = a.client_id 
        and a.country_id = 14  
        and a.adress_principale is true

Answer 4

Try this, you have to use an alias for your subquery :

update T_CLIENT c set taxe_income = true
where c.id in (
    (
    select c1.id from T_CLIENT c1, T_ADRESS a
    where c1.id = a.client_id and a.country_id = 14  and a.adress_principale is true
    ) as tmptable
);

For more information, read this : MySQL Error 1093 - Can't specify target table for update in FROM clause