December 21st, 2012, 09:14 PM
Variable table name in SELECT statement
What is the way to write a SELECT statement to retrieve data from several tables with the same name pattern. I'd like to select data from these tables: table_2012, table_2013, table_2014... I only want the tables that have the prefix (e.g. table_) and exactly the four characters for the year. I don't want tables that have a different pattern than the year. Is this doable in MySQL? What is the
SELECT * FROM TABLE LIKE table_% WHERE ....
December 21st, 2012, 09:49 PM
Not like that no.
The real problem is that you have a bunch of tables all for specific years. You really should be storing everything in one table, and including an extra column to indicate the year (if there isn't already something you can use).
You may be able to create VIEWs for each of the tables you're deprecating.
If that doesn't work I'd even consider using triggers (per-year tables have INSERT/UPDATE triggers to copy the action in the main table, main table has basically the same but going into the per-year tables) just so that you can collect all the data in one master location.
CREATE VIEW `table_2012` AS SELECT * FROM `table` WHERE `yearValue` = 2012;
But to answer the question you can do a UNION between all of them. (Still have to write the queries though.)
With a "UNION" alone MySQL will try to remove duplicates for you. If you want them, or know that there won't be any, "UNION ALL" will instruct it not to do so.
Last edited by requinix; December 21st, 2012 at 09:53 PM.
December 22nd, 2012, 11:23 AM
These are all good suggestions. But I was wondering whether there was a way to define the SELECT statement to look for tables with a certain pattern in their name, similar to the LIKE feature for columns.
Originally Posted by requinix
December 22nd, 2012, 11:59 AM
Originally Posted by mmmosias
you could do it with an application language like php, by reading the information_schema tables to build the query, but that's two separate steps