CODEWITHSUNDEEP
mysqlselect
Ilya Gazman
Ilya Gazman

Reputation: 32271

MySql select in to two variables

I got a table of ids from 1 to 1000. How can I select to rows from my table and insert the result in to two variables that I declare?

Some thing like this:

Select from table1 where id = 1 or id = 27 into var1, var2

Upvotes: 0

Views: 195

Answers (2)

Puggan Se
Puggan Se

Reputation: 5846

SELECT
    @var1 := MAX(IF(id = 1, some_field, NULL)),
    @var2 := MAX(IF(id = 27, some_field, NULL))
FROM table
WHERE id IN (1,27);

Upvotes: 1

Shlomi Noach
Shlomi Noach

Reputation: 9414

You can do that, but you need to confirm to the following two limitations:

  1. The result set must contain exactly one row
  2. The row must contain exactly two columns

See this:

SELECT 'foo', 4 FROM DUAL INTO @a, @b;

SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
| foo  |    4 |
+------+------+

And also possible:

SELECT MIN(id), MAX(Population) FROM world.City INTO @min_id, @max_pop;

SELECT name, population FROM world.Country LIMIT 1 INTO @name, @pop;

Upvotes: 1

Related Questions