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