Search code examples
mysqltemp-tables

How to creat a temporary table using existing tables with their records in MySQL?


How to create a temporary table using existing tables with their records in MySQL? These are five tables and I want to apply an UPDATE query. So when I run the query how it finds the real table and update it.

_________________
table1 VALUES   |
|  ID | NAMES   |
|  1  | name1   |
|  2  | name2   |
|  3  | name3   |
|  4  | name4   |
|  5  | name5   |
|  6  | name6   |
|  7  | name7   |
________________|
table2 VALUES   |
|  ID | NAMES   |
|  8  | name8   |
|  9  | name9   |
|  10 | name10  |
|  11 | name11  |
|  12 | name12  |
|  13 | name13  |
|  14 | name14  |
________________|
table3 VALUES   |
|  ID | NAMES   |
|  15 | name15  |
|  16 | name16  |
|  17 | name17  |
|  18 | name18  |
|  19 | name19  |
|  20 | name20  |
|  21 | name21  |
________________|
table4 VALUES   |
|  ID | NAMES   |
|  22 | name22  |
|  23 | name23  |
|  24 | name24  |
|  25 | name25  |
|  26 | name26  |
|  27 | name27  |
|  28 | name28  |
________________|
table1 VALUES   |
|  ID | NAMES   |
|  29 | name29  |
|  30 | name30  |
|  31 | name31  |
|  32 | name32  |
|  33 | name33  |
|  34 | name34  |
|  35 | name35  |
________________|

And I want to list them in temporary file like that:

_________________
TEMP VALUES     |
|  ID | NAMES   |
|  1  | name1   |
|  2  | name2   |
|  3  | name3   |
|  4  | name4   |
|  5  | name5   |
|  6  | name6   |
|  7  | name7   |
|  8  | name8   |
|  9  | name9   |
|  10 | name10  |
|  11 | name11  |
|  12 | name12  |
|  13 | name13  |
|  14 | name14  |
|  15 | name15  |
|  16 | name16  |
|  17 | name17  |
|  18 | name18  |
|  19 | name19  |
|  20 | name20  |
|  21 | name21  |
|  22 | name22  |
|  23 | name23  |
|  24 | name24  |
|  25 | name25  |
|  26 | name26  |
|  27 | name27  |
|  28 | name28  |
|  29 | name29  |
|  30 | name30  |
|  31 | name31  |
|  32 | name32  |
|  33 | name33  |
|  34 | name34  |
|  35 | name35  |
________________|

How can I do it? Here's a sqlfiddle for the test tables.


Solution

  • Code below will return your data in desired format. Then you can decide what do you want to put it into. Create a table or a view with CREATE TABLE | VIEW.

    select ID, Name from Table1
    UNION ALL
    select ID, Name from Table2
    UNION ALL
    select ID, Name from Table3
    UNION ALL
    select ID, Name from Table4
    UNION ALL
    select ID, Name from Table5