How to select from dynamic table name
15,279
You open 1 (
and close 2 )
. Remove the last:
SELECT CONCAT('changes',year,month) FROM changes
Edit
the second statement should probably be
SET @x := SELECT * FROM (@b) as b;
That works, but not sure if that is what you want:
SET @b := 'SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges';
SET @x := 'SELECT * FROM (SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges) as b';
Prepare stmt FROM @b;
Prepare stmt FROM @x;
Execute stmt;
Edit2
If I understood you right you are looking for that single query:
select * from changes
where change_column in (select distinct concat(`year`, `month`) from whichchanges)
Edit3
select @b := group_concat(concat(' select * from changes', `year`, `month`, ' union ') separator ' ') as w from whichchanges;
set @b := left(@b, length(@b) - 6);
Prepare stmt FROM @b;
Execute stmt;
Related videos on Youtube
Author by
breq
"Symofny code guru ninja" ( ͡º ͜ʖ͡º), lenny facies lover <3, freelancer, php developer and acrive user of stack overflow
Updated on June 04, 2022Comments
-
breq about 2 years
I have tables like
changes201101 changes201102 changes201103 ... changes201201
And table
whichchanges
which contain rows Year and MONTHHow I can select * from changes from
whichchanges
?I type this query
SET @b := SELECT CONCAT('changes',year,month) FROM whichchanges; ((((@b should contain now multiple rows of changesYearMonth))))) SET @x := SELECT * FROM @b; Prepare stmt FROM @b; Prepare stmt FROM @x; Execute stmt;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT('changes',year,month) FROM changes)' at line 1
-
juergen d about 12 yearsLooks like you edited your question. Now the query doesn't fit to your error message any more.
-
-
breq about 12 years
SET @b = SELECT CONCAT('change',year,month) FROM whichchanges; SET @x := SELECT * FROM (@b); Prepare stmt FROM @b; Prepare stmt FROM @x; Execute stmt; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT('changes',year,month) FROM whichchanges' at line 1
-
breq about 12 yearsif i use
"SET @b = (SELECT CONCAT('changes',year,month) FROM whichchanges);" **#1242 - Subquery returns more than 1 row
** if i useSET @b = (SELECT CONCAT('changes',year,month) FROM whichchanges LIMIT 1);
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM (@b)' at line 1 -
breq about 12 yearsi hope this will help i.imgur.com/hni0g.jpg if not, I'll do it by using PHP
-
breq about 12 years@jurgen-d, yep, it must be in one query but what is "change_column" in your query? I think that query should be something like "select * from changes where changes in (select distinct concat('changes ',
yer
,month
) from whichchanges)" #1146 - Table 'database.changes' doesn't exist -
juergen d about 12 yearsYou should replace
change_column
with the column name that holds the values in format201201
of yourchanges
table. -
juergen d about 12 years@breq: See this SQLFiddle Example. Is that correct? If not, correct it and post back the SQLFiddle link.
-
juergen d about 12 years@breq: See my last update with this example.
-
breq about 12 yearslast questions, why i've got comma between union and select? it does not work on my pc :/ #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' select * from changes200804 union , select * from changes200805 union , select * ' at line 1 i've change tab names on my pc, but on sqlfiddle it work fine with my real tab names
-
breq about 12 years