CODEWITHSUNDEEP
mysql
Dylan
Dylan

Reputation: 137

Selecting more than one row using mysql

MySql table

CREATE TABLE cities (city VARCHAR(30)); 
  INSERT INTO cities VALUES ('St. Louis');
  INSERT INTO cities VALUES ('San Diego');
  INSERT INTO cities VALUES ('Seattle');

MySql Query

DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities where name ='Seattle' limit 1;

By using the above query, I am able to select city name into @name variable. But when I am able to select multiple values:

MySQL Query

DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities;

We are getting exception as below:

Error Code: 1172. Result consisted of more than one row

How to select multiple values using select into command. Thanks

Upvotes: 1

Views: 327

Answers (2)

elbaridi
elbaridi

Reputation: 11

You need to declare a temporary table and fill it with SELECT of another table:

CREATE TEMPORARY TABLE name(city varchar(30)); 
INSERT INTO name SELECT cities FROM cities;

Upvotes: 1

GMB
GMB

Reputation: 222722

You can't select multiple values in a single string variable. If you are content with a CSV list of values, you can use group_concat():

declare @names varchar(30);
select @names = group_concat(city) from cities;

Upvotes: 1

Related Questions