How to select from MySQL where Table name is Variable

Question

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.

Joe Stefanelli · Accepted Answer · 2012-01-10 20:25:24Z

96

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;

answered Jan 10, 2012 at 20:25

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

8 Comments

Rosmarine Popcorn Over a year ago

One more thing it show resul only when i remove DEALLOCATE PREPARE stm1;

Tim Visée Over a year ago

Brilliant solution. Helped me to solve the issue I'm having, thanks.

Frozen Flame Over a year ago

What does the DEALLOCATE PREPARE do?

ruzenhack Over a year ago

@FrozenFlame, release the stmt1. If you do not release it, may you encounter the max limit of statements, enforced by max_prepared_stmt_count variable system, how you can read here.

oldboy Over a year ago

is this still valid syntax in 2018??

|

Collectives™ on Stack Overflow

How to select from MySQL where Table name is Variable

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related