Insert to table from table - mulitple rows fail....

[scotchegg78]

HI guys,

 

My code here fails as there are multiple rows, I thought this should work??

 

insert into company_sa_country(countryid,companyid) VALUES ('1',(SELECT companyfk FROM company_region cr WHERE cr.regionfk = '94'))

 

ITs simply taking companys with old region ids that are 94 and placing them the new table with ids 1.

 

thanks for any info.

 

[cooldude832]

well it should haev multiple rows if the SELECT returns multiple rows

 

I believe it will treat your string value '1' as a constant and pair it with the number of matching results for the select query.

 

If its giving an error that message would be advantageous to solving your problems, otherwise its working as its built, maybe not as you like.

 

If you need to select only 1 specific company then you need to have a stronger where clause or apply a LIMIT to your query.

[scotchegg78]

Hi thanks for reply.

 

It should return multiple rows, but it fails with this multiple rows error?

 

I need to it to insert all rows from the table with the id 94 into the new table with its company ID and a new forein key as 1.

 

Like the query is laid out, only it wont run

 

if it help here is the table info..

 

From table... company_region (id,companyfk,regionfk)

to table  .. company_sa_county(id,companyid,countyid).

 

ids's are auto, companyfk and companyid are the same company id values, and i replace regionfk where 94 with 1 into company_sa_county.

 

[fenway]

That subquery can only take a single value back... perhaps you need distinct.