1

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

fancyPants
  • 50,732
  • 33
  • 89
  • 96
Ashish Pandit
  • 137
  • 2
  • 5
  • 1
    You can trick MySQL with another subquery if you want to select from a table you are also updating. – juergen d Jan 10 '14 at 17:49
  • Possible duplicate of: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Ritesh May 22 '15 at 06:26

1 Answers1

2

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;
Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
  • When I run above query, it gives me following error - Error Code: 1093. You can't specify target table 'A' for update in FROM clause. When I enclose it in another select statement, I get the error stating Error Code 1062: Duplicate entry '4844' for key primary. – Ashish Pandit Jan 14 '14 at 05:47
  • I made a change. a bit sloppy of me at first, but now its joining the table instead of using it in the actaul update. This should work, let me know! – Mad Dog Tannen Jan 14 '14 at 07:18