Subquery returns more than 1 row

8 Message(s) by 2 Author(s) originally posted in mysql discussion

From: UKuser

Date: Monday, October 22, 2007

Hi,

What am I doing wrong with this statement?

UPDATE company
SET

A collection of objects, known as the elements of the set,
	specified in such a way that we can tell in principle whether
	or not a given object belongs to it.  E.g. the set of all prime
	numbers, the set of zeros of the cosine function.

	For each set there is a {predicate} (or property) which is
	true for (possessed by) exactly those objects which are
	elements of the set.  The predicate may be defined by the set
	or vice versa.  Order and repetition of elements within the
	set are irrelevant so, for example, {1, 2, 3} = {3, 2, 1} =
	{1, 3, 1, 2, 2}.

	Some common set of numbers are given the following names:

	N = the {natural numbers} 0, 1, 2, ...

	Z = the {integers} ..., -2, -1, 0, 1, 2, ...

	Q = the {rational numbers} p/q where p, q are in Z and q /= 0.

	R = the {real numbers}

	C = the {complex numbers}.

	The empty set is the set with no elements.  The intersection
	of two sets X and Y is the set containing all the elements x
	such that x is in X and x is in Y.  The union of two sets is
	the set containing all the elements x such that x is in X or x
	is in Y.

	See also {set complement}.

	(1995-01-24)

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

<data, database> An area of a {database} {record}, or
	{graphical user interface} {form}, into which a particular
	item of data is entered.

	Example usage: "The telephone number field is not really a
	numerical field", "Why do we need a four-digit field for the
	year?".

	A {database} {column} is the set of all instances of a given
	field from all records in a {table}.

	(1999-04-26)

new_test separated by a #
but it error

1. A discrepancy between a computed, observed, or measured
	value or condition and the true, specified, or theoretically
	correct value or condition.

	2. <programming> A mental mistake made by a programmer that
	may result in a program {fault}.

	3. (verb) What a program does when it stops as result of a
	programming error.

	(2000-03-28)

s on me.

Any help'd be great.

Thanks

A

From: Captain Paralytic

Date: Monday, October 22, 2007

wrote in message:

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 help'd 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 is not.

So,'d you care to tell us!

From: UKuser

Date: Monday, October 22, 2007

<data, database, programming> An {ordered set} of {fields},
	usually stored contiguously.  The term is used with similar
	meaning in several different contexts.  In a file, a "record"
	probably has some fixed length, in contrast to a "line" which
	may have any length and is terminated by some {End Of Line}
	sequence).  A {database} record is also called a "row".  In a
	{spreadsheet} it is always called a "row".  Some programming
	languages use the term to mean a type composed of fields of
	several other types ({C} calls this a "{struct}").

	In all these cases, a record represents an entity with certain
	field values.

	Fields may be of a fixed width ({bits} or {characters}) or
	they may be separated by a {delimiter} character, often
	{comma} ({CSV}) or {HT} ({TSV}).

	In a database the list of values of a given field from all
	records is called a column.

	(2002-03-22)

" is the error :o)

From: Captain Paralytic

Date: Monday, October 22, 2007

wrote in message:

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 help'd 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 aren't a good idea)?
Next, why should not the subquery return more than one row?

From: UKuser

Date: Monday, October 22, 2007

wrote in message:
wrote in message:
> 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 help'd 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 aren't a good idea)?
Next, why should not 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.

From: Captain Paralytic

Date: Monday, October 22, 2007

wrote in message:
"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.

From: Captain Paralytic

Date: Monday, October 22, 2007

wrote in message:
wrote in message:
wrote in message:
> > 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 help'd 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 aren't a good idea)?
> Next, why should not 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

1. Executable code, especially a "pure code" portion shared
	between multiple instances of a program running in a
	{multitasking} {operating system}.

	Compare {English}.

	2. Textual material in the mainstream sense; data in ordinary
	{ASCII} or {EBCDIC} representation (see {flat ASCII}).  "Those
	are text files; you can review them using the editor."

	These two contradictory senses confuse hackers too.

	[{Jargon File}]

	(1995-03-16)

-
- Show quoted text -

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

From: UKuser

Date: Monday, October 22, 2007

wrote in message:
wrote in message:
wrote in message:
wrote in message:
> > > 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 help'd 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 aren't a good idea)?
> > Next, why should not 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

Next Message: Data driven relations browsing

Blogs related to Subquery returns more than 1 row

This website will help you to find out everything you need to know ...
knee ligaments The sims hot date free. boyd company louise bell graves diease urethra play gallery purple crocs/uk f - 4 phantom ii freelancer.com sql subquery clamp on ball mount macromedia dreamweaver training village surf shop pci ...

c limited epocast 1652 topowest triad engineering calgary badan ...
... heal montparnasse hotels pukka electric minibike drinking age 18 21 acid base titration wine traurigkeit nyc department of finance parking tax exemption correlated subquery db2 california fresno online radio repairing yamaha engines ...

i have b2evolution a database design question - i want to store ...
while($row=mysql…) { mysql_query(”something with $row[’id’]..”); } Then you need to be using a table join in a single query Haha… hmm.. I just logged into a machine which runs fortune when you log in. My fortune: If you only have a ...

I ma looking for suggestion I have C program that downloads some ...
Well, the MySQL MO is to see how much nerd rage they can generate without people switching. You have to scream but not leave. SCO, the initial split, the removal of the tarballs, it all flows together. don't forget Area 51, ...

Good morning all We recently upgraded from MySQL 5022-log to MySQL ...
im still getting "SQL/DB Error — [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]" how do I turn off these errors? ...