CODEWITHSUNDEEP
mysqlsql
Walter Schrabmair
Walter Schrabmair

Reputation: 1341

How to update a table with more than one value

I have a table refs with citations (AU NAMES, which are separated by ; in one field authors) and a table called aunames with the AU-NAMES at each row. Now I would like to update my table aunames with the total number of authors for each citation.

 rec  ID  NAME  AUCOUNT
  1   3   AU1   
  2   3   AU2 
  ...
  ...

How can I do that? (auname_copy is a copy of auname.) I tried:

 update aunames 
 set aucount = (select count(rec) 
                from refs 
                join aunames_copy on refs.id=aunames_copy.id  
                GROUP BY refs.id) ;

But I get the error:

[Err] 1242 - Subquery returns more than 1 row

WHen I try only one row it works.

update aunames 
set aucount = (select count(rec) 
               from refs 
               join aunames_copy on refs.id=aunames_copy.id 
               where refs.id='1'  
               GROUP BY refs.id ) 
where id='1';

How can I loop through all rows? Thanks

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222472

You can use the UPDATE ... JOIN ... SET syntax:

UPDATE aunames a
INNER JOIN (
    SELECT r.id, count(rec) cnt
    FROM refs r
    INNER JOIN aunames_copy c ON r.id = c.id  
    GROUP BY r.id
) x ON x.id = a.id
SET a.aucount = x.cnt

NB: if, as commented by @MadhurBhaiya, you created table aunames_copy as a copy of original table aunames, your query can probably be simplified as:

UPDATE aunames a
INNER JOIN (SELECT id, count(rec) cnt FROM refs GROUP BY id) x ON x.id = a.id
SET a.aucount = x.cnt

Upvotes: 1

Related Questions