How to select from MySQL where Table name is Variable

mysql set tablename
Rosmarine Popcorn picture Rosmarine Popcorn · Jan 10, 2012 · Viewed 62.2k times · Source

I have a case where getting the table name should be from a set variable like: 

SET @ID_1 = (SELECT ID FROM `slider` LIMIT 0,1);
SET @Cat = (SELECT Category FROM `slider` LIMIT 0,1);
select * from @Cat where ID = @ID_1

but doing that way MySQL outputs an error, so could someone show me how I can achieve that, because these are my baby steps in MySQL.

Answer

Joe Stefanelli picture Joe Stefanelli · Jan 10, 2012

You'd have to do this with a prepared statement. Something like:

SET @s = CONCAT('select * from ', @Cat, ' where ID = ', @ID_1); 

PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

Related questions

How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More
How to store Query Result in variable using mysql

SET @v1 := SELECT COUNT(*) FROM user_rating; SELECT @v1 When I execute this query with set variable this error is shown. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server …

Read More
MySQL local variables

I am trying to define and initialize a MySQL variable for a query. I have the following: declare @countTotal int; SET @countTotal = select COUNT(*) from nGrams; I am using MySQL in Netbeans and it tells me I have an error. …

Read More