CODEWITHSUNDEEP
mysql
Fabio Manniti
Fabio Manniti

Reputation: 171

MySQL Declare column to select as variables

I don't know how to ask this question but I would like to know if is it possible to declare the column names I want to get from a select.

Let's say I have a query like this:

SELECT field_1, field_2, field_3
FROM table_1
UNION
SELECT field_1, field_2, field_3
FROM table_2
UNION
SELECT field_1, field_2, field_3
FROM table_3
...

I want to know if I can do something like

SELECT @fields
FROM table_1
UNION
SELECT @fields
FROM table_2
UNION
SELECT @fields
FROM table_3
...

Upvotes: 0

Views: 171

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562320

You can't do this without using dynamic SQL. The columns in a query must be named explicitly at the time the query is parsed.

You could do this:

SET @query = CONCAT(
 'SELECT ', @fields,
 ' FROM table_1',
 ' UNION',
 ' SELECT ', @fields,
 ' FROM table_2',
 ' UNION ',
 ' SELECT ', @fields,
 ' FROM table_3',
 ...
);

PREPARE stmt FROM @query;
EXECUTE stmt;

This way all the columns are named explicitly in the @query string by the time you use PREPARE to parse it.

But it's up to you to make sure @fields contains a comma-separated list of column names that exist in those tables, so the resulting query is valid.

Upvotes: 1

Related Questions