mysql merge rows from multiple tables

Question

I have a mysql database that contains multiple tables with the same columns (not my design decision). These tables are named dynamically like codes_1, codes_2.... I would like to execute a query that will take the contents (rows) of codes_1, and codes_2..., and merge them together into one result.

I already have sql that can return the names of the tables using SCHEMA_INFORMATION.table_name, but I cannot figure out how to query each table based on the name, and how to merge the result of each table into one result.

So in reality here are the two questions:

how can I query multiple tables based on the table names that are returned as a query result?
how can I, after doing a select on each table, merge all the rows from the tables into one result?

What do you mean by "merge them into one result". Do you mean add them together? Or something else? An example would go a long way here... — lurker
– lurker, Commented Jan 25, 2018 at 17:57
Look into UNION. But also post the schema, or even a screenshot of the tables so we can see the columns in each (i know you said they are the same but its really helpful to be able to see it...) — Stuart
– Stuart, Commented Jan 25, 2018 at 17:57
@lurker I will have one query that will get the table names. then another query that will get all columns for each table with a name in the first query results. That will result in rows from table "codes_1", rows from table "codes_2", etc. I will then need to "merge" all of these rows together to return them. — Nick Sallis
– Nick Sallis, Commented Jan 25, 2018 at 18:10
Maybe something from these answers will help: MySQL Loop Through Tables and How to loop through all the tables on a database to update columns. These solutions don't directly answer your specific scenario, but the principles may get you there. In the end, you might end up creating a new table and iterating through the others, selecting from them and updating the new table with the results. — lurker
– lurker, Commented Jan 25, 2018 at 18:49

Ottawa · Accepted Answer · 2018-01-25 18:00:28Z

3

Is this what you're looking for? Edit the colums to your preference, you never specified your table names so I just named them table_1 and table_2.

SELECT codes_1, codes_2, codes_3 FROM `table_1`
UNION 
SELECT codes_1, codes_2, codes_3 FROM `table_2`

answered Jan 25, 2018 at 18:00

Ottawa

1244 bronze badges

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

2 Comments

Nick Sallis Over a year ago

codes_1 and codes_2 are table names. there might also be hundreds more. I get the names as a sql query to SCHEMA_INFORMATION since the names are all similar save the last number.

Nick Sallis Over a year ago

FYI, this is NOT the answer. Codes_1, codes_2... are TABLE NAMES, not column names!

Collectives™ on Stack Overflow

mysql merge rows from multiple tables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related