Is there a way to automatically change which table to run my query?-CodePudding

SET @UserID = 21077;
SELECT*FROM DATA.tbl_7 where UserID = @UserID

currently User data are separated based on which number it ends with for example if it ends with 1, I have to run my query on DATA.tbl_1 which means every time I want to run query on each User ID # I have to change my query lines. for example if I want to look up User ID of 21079

SET @UserID = 21079;
SELECT*From DATA.tbl_9 where UserID = @UserID

changing "tbl_#" every time is really bothering me.

I have tried

SET @UserID = 21079;
SET @UserIDTABLELOOKUP = (SELECT RIGHT(@UserID, 1));
SELECT*FROM DATA.tbl_ (@UserIDTABLELOOKUP) where UserID = @UserID;

but it does not work for obvious reason. Would there be a way to set which data table to "Select*From" data table based on UserID I set?

CodePudding user response：

Try something like this:

SET @UserID = 21079;
SET @UserIDTABLELOOKUP = (SELECT RIGHT(@UserID, 1));
SET @script = CONCAT('SELECT * FROM DATA.tbl_', @UserIDTABLELOOKUP, ' WHERE UserID = ?');
PREPARE stmt FROM @script;
EXECUTE stmt USING @UserID;
DEALLOCATE PREPARE stmt;