CODEWITHSUNDEEP
mysql
Ashish Pandit
Ashish Pandit

Reputation: 147

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

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

Upvotes: 1

Views: 4363

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

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;

Upvotes: 2

Related Questions