Hi,

What am I doing wrong with this statement?

UPDATE company
SET new_test =
concat_ws(
(
Select Property from `properties` where `Company_ ID`=1
)
,'#')
WHERE
company.`Company_ ID`=1

I want it to insert Property into the field new_test separated by a #
but it errors on me.

Any would be great.

Thanks

A

On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
> Hi,
>
> What am I doing wrong with this statement?
>
> UPDATE company
> SET new_test =
> concat_ws(
> (
> Select Property from `properties` where `Company_ ID`=1
> )
> ,'#')
> WHERE
> company.`Company_ ID`=1
>
> I want it to insert Property into the field new_test separated by a #
> but it errors on me.
>
> Any would be great.
>
> Thanks
>
> A

"it errors on me"
Wow, how bl**dy useful. If my crystal ball was working I could of
course see what error you were getting. But it isn't.

So, would you care to tell us!

On 22 Oct, 15:55, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
>
>
>
> > Hi,
>
> > What am I doing wrong with this statement?
>
> > UPDATE company
> > SET new_test =
> > concat_ws(
> > (
> > Select Property from `properties` where `Company_ ID`=1
> > )
> > ,'#')
> > WHERE
> > company.`Company_ ID`=1
>
> > I want it to insert Property into the field new_test separated by a #
> > but it errors on me.
>
> > Any would be great.
>
> > Thanks
>
> > A
>
> "it errors on me"
> Wow, how bl**dy useful. If my crystal ball was working I could of
> course see what error you were getting. But it isn't.
>
> So, would you care to tell us!

"Subquery returns more than 1 row" is the error )

On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
> Hi,
>
> What am I doing wrong with this statement?
>
> UPDATE company
> SET new_test =
> concat_ws(
> (
> Select Property from `properties` where `Company_ ID`=1
> )
> ,'#')
> WHERE
> company.`Company_ ID`=1
>
> I want it to insert Property into the field new_test separated by a #
> but it errors on me.
>
> Any would be great.
>
> Thanks
>
> A

Well, for a start, why are you using CONCAT_WS rather than CONCAT?
Next, are you sure that `Company_ ID` is really the column name
(spaces in column names are not a good idea)?
Next, why shouldn't the subquery return more than one row?

On 22 Oct, 16:03, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
>
>
>
> > Hi,
>
> > What am I doing wrong with this statement?
>
> > UPDATE company
> > SET new_test =
> > concat_ws(
> > (
> > Select Property from `properties` where `Company_ ID`=1
> > )
> > ,'#')
> > WHERE
> > company.`Company_ ID`=1
>
> > I want it to insert Property into the field new_test separated by a #
> > but it errors on me.
>
> > Any would be great.
>
> > Thanks
>
> > A
>
> Well, for a start, why are you using CONCAT_WS rather than CONCAT?
> Next, are you sure that `Company_ ID` is really the column name
> (spaces in column names are not a good idea)?
> Next, why shouldn't the subquery return more than one row?

I want it to return every corresponding row and insert it into my new
field. And unfortunately yes Company_ ID is the correct name.
As it stands it only inserts 1 of the fields into my new field rather
than however many are returned by the SELECT statement.

On 22 Oct, 16:08, UKuser <spiderc...@yahoo.co.uk> wrote:
"As it stands it only inserts 1 of the fields into my new field rather
than however many are returned by the SELECT statement."

Your select statement only contains 1 field.

On 22 Oct, 16:08, UKuser <spiderc...@yahoo.co.uk> wrote:
> On 22 Oct, 16:03, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
>
> > > Hi,
>
> > > What am I doing wrong with this statement?
>
> > > UPDATE company
> > > SET new_test =
> > > concat_ws(
> > > (
> > > Select Property from `properties` where `Company_ ID`=1
> > > )
> > > ,'#')
> > > WHERE
> > > company.`Company_ ID`=1
>
> > > I want it to insert Property into the field new_test separated by a #
> > > but it errors on me.
>
> > > Any would be great.
>
> > > Thanks
>
> > > A
>
> > Well, for a start, why are you using CONCAT_WS rather than CONCAT?
> > Next, are you sure that `Company_ ID` is really the column name
> > (spaces in column names are not a good idea)?
> > Next, why shouldn't the subquery return more than one row?
>
> I want it to return every corresponding row and insert it into my new
> field. And unfortunately yes Company_ ID is the correct name.
> As it stands it only inserts 1 of the fields into my new field rather
> than however many are returned by the SELECT statement.- Hide quoted text -
>
> - Show quoted text -

I have a feeling that you are looking for GROUP_CONCAT()

On 22 Oct, 16:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 Oct, 16:08, UKuser <spiderc...@yahoo.co.uk> wrote:
>
>
>
> > On 22 Oct, 16:03, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 22 Oct, 15:33, UKuser <spiderc...@yahoo.co.uk> wrote:
>
> > > > Hi,
>
> > > > What am I doing wrong with this statement?
>
> > > > UPDATE company
> > > > SET new_test =
> > > > concat_ws(
> > > > (
> > > > Select Property from `properties` where `Company_ ID`=1
> > > > )
> > > > ,'#')
> > > > WHERE
> > > > company.`Company_ ID`=1
>
> > > > I want it to insert Property into the field new_test separated by a #
> > > > but it errors on me.
>
> > > > Any would be great.
>
> > > > Thanks
>
> > > > A
>
> > > Well, for a start, why are you using CONCAT_WS rather than CONCAT?
> > > Next, are you sure that `Company_ ID` is really the column name
> > > (spaces in column names are not a good idea)?
> > > Next, why shouldn't the subquery return more than one row?
>
> > I want it to return every corresponding row and insert it into my new
> > field. And unfortunately yes Company_ ID is the correct name.
> > As it stands it only inserts 1 of the fields into my new field rather
> > than however many are returned by the SELECT statement.- Hide quoted text -
>
> > - Show quoted text -
>
> I have a feeling that you are looking for GROUP_CONCAT()

Many thanks for that

UPDATE company
SET new_test =
(
Select GROUP_CONCAT(Property)
from `properties`
where properties.`Company_ ID`=company.`Company_ ID`
)

Has fixed it