I have a query that uses a variable in its where clause:
SET @id = 1;
SELECT
id,
value
FROM myTable
WHERE id = @id;
I would like to run the query for @id values 1 through 100, and then union (or somehow combine) all the loop results into one result set. Is this possible in MySQL, and if so, what is a good approach?
Why would use use a variable for this? Just use a simple where clause:
select id, value
from myTable
where id between 1 and 100;
If, instead, you really want the first 100 rows by id, then use order by and limit:
select id, value
from myTable
order by id
limit 100;
Just use a subquery to get the variable.
And put the where outside
SELECT *
FROM
(
SELECT
@rownumber := if(@rownumber, @rownumber + 1, @rownumber + 1) AS id,
value
FROM myTable
CROSS JOIN (select @rownumber := 0) r
) as T
WHERE id < 100;
@rownumber := if(@rownumber, @rn + 1, @rn + 1) AS id, fix it?the order of evaluation