CODEWITHSUNDEEP
mysqlsqlvariablesselect
SamanthaAlexandria
SamanthaAlexandria

Reputation: 225

Select a table when table name has been stored as a variable

Using MYSQL, how can I select a table whose name has been stored as a variable? For instance, I have 2 tables (T1 and T2) and I want to derive T3:

DECLARE i INT; DECLARE n INT;
DECLARE o VARCHAR(50);    
DECLARE T3 VARCHAR(50);

SET n=(SELECT COUNT(*) FROM `T1`);
i=1
WHILE i<n+1 DO
SET o=(SELECT customer FROM `T1` WHERE IDligne=i); 
SET T3=(SELECT MiddleCat FROM `T2` WHERE customer=o); 
SET x = SELECT * FROM `T3`; 

SET i=i+1;
END WHILE;
END

I want to set the variable (x) to be equal to the result of the T3 query (to retrieve the name of other tables):

But this shows an error. What query should I used instead?

Upvotes: 0

Views: 72

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You should use a prepared statement. You can use a session variable to store the result of your query. In the example below, I assign the result of a count query into a variable @result.

DECLARE @query VARCHAR(50);
SET @result := 0;
SET T3 = (SELECT MiddleCat FROM `T2` WHERE customer=o);
SET @query = CONCAT('SELECT COUNT(*) INTO @result FROM ', T3);
PREPARE stmt FROM @query;
EXECUTE stmt;

Highly relevant: Using select into local variable and prepared statement in mysql

Upvotes: 1

UJS
UJS

Reputation: 861

Simply in your case you can user either of the following :

 1. Print T3 
 2. Select T3

Upvotes: 0

Related Questions