[Err] 1242 - Subquery returns more than 1 row in an update assignment

[antoine1987]

Hi

I am trying to run this code in order to fill the reasults in the column

[CODE=mysql]update test
set activate = (select distinct
count(sigmessag es.messagetype)
from
sigmessages
WHERE
sigmessages.mes sagetype = 33 and
sigmessages.spe echordata = 1 and
sigmessages.cbi ts <= 3
group by cxid, e1id, sigchannel)
;[/CODE]

I have this error message:
[Err] 1242 - Subquery returns more than 1 row

please i need help to resolve this problem

thanks

[code green]

Run the sub-query on its own

select distinct 
count(sigmessages.messagetype)
from
sigmessages
WHERE
sigmessages.messagetype = 33 and
sigmessages.speechordata = 1 and
sigmessages.cbits <= 3
group by cxid, e1id, sigchannel)

And more than one result set is being returned.
So this update

update test
set activate =

is impossible because activate can only be set to one value.
That is what the error is telling you

[antoine1987]

thank you code green

but there is no solution to insert all the results in the column?

[mwasif]

To insert the data you need INSERT INTO... SELECT. e.g.

[CODE=mysql]INSERT INTO test (activate)
select distinct
count(sigmessag es.messagetype)
from
sigmessages
WHERE
sigmessages.mes sagetype = 33 and
sigmessages.spe echordata = 1 and
sigmessages.cbi ts <= 3
group by cxid, e1id, sigchannel;[/CODE]

[Oralloy]

You have a fundamental difference in solutions.

@antoine1987 initially showed us an UPDATE, which changes existing rows in the TEST table.

@mwasif showed reasonably how to diagnose antoine's problem.

@mwasif also showed an INSERT to place multiple rows into TEST.

The question is - what is it that you are trying to do Antione? UPDATE and INSERT are two fundamentally different operations.

[antoine1987]

hi Oralloy
i know that update and insert have different results,

my problem is that i have a principal table containing records, and i want to concatenate a column from another table that also contains records to the principal one.
i know it is complicate :(

[Oralloy]

@antoine1987,

Are you updating the principle table, or adding records to it?

If you're UPDATEing, then you can only have one value per updated column (per record).

If you're INSERTing records, then you can put in as many separate records as you want.

If you want to put a collection of results into a single column as part of an UPDATE, then you'll have to use some sort of stored procedure or program logic.

That's why I'm asking.