How to use Select to assign array to a MySql variable

Question

This command works:

SET @a = '1,2,3,4,5,6,7,8,9';
SELECT Id FROM MyTable2 WHERE find_in_set(Id, @a)

I would like to know if it's possible to use a SELECT instead of '1,2,3,4,5,6,7,8,9' because I wasn't able to do it.

I've tryed:

SET @a = CONCAT_WS(',', (SELECT Id FROM MyTable1 WHERE Id < 10));

but I get error:

Subquery returns more than 1 row

Is there a way to do what I'm trying?

@Strawberry I need to use the value in 3 queries so if I have it into a variable I'll avoid 3 subqueries — genespos
– genespos, Commented Apr 7, 2016 at 14:28

Giorgos Betsos · Accepted Answer · 2016-04-07 14:20:16Z

2

Try this way:

SET @a = (SELECT GROUP_CONCAT(Id) FROM mytable WHERE Id < 10);

This will set @a to a string like '1, 2, 3, ...'.

answered Apr 7, 2016 at 14:20

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1