MySQL Error Code: 1093. You can't specify target table 'xyz' for update in FROM clause

Question

I am trying to write query against following table

Table A
========
ID
A1


Table B
=========
ID
B1

Query 1:

update A
set A.A1 =(
select count (distinct b1)
from B
join A on A.ID = B.ID
Group by B1

gives me an error -

MySQL Error Code: 1093. you can't specify target table for update in FROM clause

Hence I tried below query as below:

Query 2:

update A
set A1 = (
  select count from (
    select count( distinct B1) as count
        from B 
        join A on A.ID = B.ID
        group by ID
  ) s
);

Above query gives me error

Error Code: 1242. Subquery returns more than 1 row

I tried creating staging table as below and it works.

Query 3:

insert into stg
(
par1,
par2
)
select ID, count( distinct B1) from B group by ID;


update A
set A1 =  ( select par2 from stg where A.pm = stg.par1 );

Is there a better way to do the same?

Thanks

You can trick MySQL with another subquery if you want to select from a table you are also updating. — juergen d
Possible duplicate of: stackoverflow.com/questions/45494/… — Ritesh

Mad Dog Tannen Mad Dog Tannen · Accepted Answer · 2014-01-10T18:50:37

If you are only counting without a to reference the count, then skip the GROUP BY

Change.

update A 
JOIN (select count( distinct B1) as count FROM B JOIN A on A.ID = B.ID) B
set A.A1=B.count;

MySQL Error Code: 1093. You can't specify target table 'xyz' for update in FROM clause

1 Answers