How to set mysql update value which from other select

Question

In my project, I have table IDCA and IDCB.

I cat query sql like:

$liId=99;
select name from  IDCA  where stId='$liId';

And the result is:

John
Stack
Shansa
Aliya
Stock

Then I set allName of IDCB with these values.

update IDCB set allName=(select name from  IDCA  where stId='$liId') where stId='$liId';

But it works fail. And error is:

ERROR 1242 (21000): Subquery returns more than 1 row

Actually, I want set allName='John,Stack,Shansa,Aliya,Stock'. So who can help me?

Try this other SO question: https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — David Brossard, Oct 23 '17 at 03:11

score 1 · Accepted Answer · edited Oct 23 '17 at 13:43

1

try to using GROUP_CONCAT()

update IDCB set allName=(
    select GROUP_CONCAT(name) 
    from  IDCA  
    where stId='$liId' GROUP BY stId)
where stId='$liId';

edited Oct 23 '17 at 13:43

David Brossard

answered Oct 23 '17 at 03:09

ferhado

1 Answers1