CODEWITHSUNDEEP
mysql
bln_dev
bln_dev

Reputation: 2411

Join tables with variable table name

I have no idea how to start here. Can you do the following?

I have 50+ tables like this

`db.events_100`:

+ id     + ts      + var       + val       +
--------------------------------------------
| 1      | 5000    | some      | thing     |
| 2      | 5050    | some      | thing     |
| 3      | 5085    | some      | thing     |

or this:

`db.events_101`:

+ id     + ts      + var       + val       +
--------------------------------------------
| 1      | 6020    | different | things    |
| 2      | 6060    | different | things    |
| 3      | 6095    | different | things    |

And I would like to migrate those tables to something like:

`db.events`:

+ id     + old_id | events_id | ts      + var       + val       +
-----------------------------------------------------------------
| 1      | 1      | 100       | 5000    | some      | thing     |
| 2      | 2      | 100       | 5050    | some      | thing     |
| 3      | 3      | 100       | 5085    | some      | thing     |
| 4      | 1      | 101       | 6020    | different | things    |
| 5      | 2      | 101       | 6060    | different | things    |
| 6      | 3      | 101       | 6095    | different | things    |

In this case events.events_id corresponds to the variable part of the table name. What would be convenient query to accomplish that, ideally without a loop.

Upvotes: 0

Views: 41

Answers (1)

bln_dev
bln_dev

Reputation: 2411

For googlers...

Indeed, I couldn't build a variable query. Instead, I "unioned" my 87 tables like this:

drop table if exists `events`;

CREATE TABLE `events` (
    id INT AUTO_INCREMENT NOT NULL, 
    event_id VARCHAR(255) NOT NULL, 
    event_ts INT NOT NULL, 
    event_type VARCHAR(255) NOT NULL, 
    event_var VARCHAR(255) NOT NULL, 
    event_val VARCHAR(255) NOT NULL, 
    event_side VARCHAR(255), 
    player_id VARCHAR(255) NOT NULL, 
    time_edit INT NOT NULL, 
    meta_author VARCHAR(255) NOT NULL,
    message VARCHAR(255) DEFAULT NULL, 
    `status` VARCHAR(255) DEFAULT NULL, 
    reported VARCHAR(255) DEFAULT NULL, 
    reported_status VARCHAR(255) DEFAULT NULL, 
    score VARCHAR(255) DEFAULT NULL, 
    user_rating VARCHAR(255) DEFAULT NULL, 
    user_upvotes VARCHAR(255) DEFAULT NULL, 
    watched VARCHAR(255),
    old_id INT
    DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

INSERT INTO `events` (
    id, event_id, event_ts, event_type, event_var, event_val,   event_side, player_id, time_edit, meta_author, message, `status`, reported, reported_status, score, user_rating, user_upvotes, watched, old_id
)

SELECT
    NULL, t.event_id, t.event_ts, t.event_type, t.event_var, t.event_val, t.event_side, t.player_id, t.time_edit, t.meta_author, t.message, t.status, t.reported, t.reported_status, t.score, t.user_rating, t.user_upvotes, t.watched, t.old_id

FROM(
    select *, 1421362800 old_id from events_1421362800 union 
    select *, 1421967600 old_id from events_1421967600 union 
    select *, 1423782000 old_id from events_1423782000 union 
    select *, 1424386800 old_id from events_1424386800 union 
    select *, 1426201200 old_id from events_1426201200 union 
    select *, 1426806000 old_id from events_1426806000 union 
    select *, 1427410800 old_id from events_1427410800 union 
    select *, 1428012000 old_id from events_1428012000 union 
    select *, 1428616800 old_id from events_1428616800 union 
    select *, 1429826400 old_id from events_1429826400 union 
    select *, 1431036000 old_id from events_1431036000 union 
    select *, 1432245600 old_id from events_1432245600 union 
    select *, 1432850400 old_id from events_1432850400 union 
    select *, 1434664800 old_id from events_1434664800 union 
    select *, 1435269600 old_id from events_1435269600 union 
    select *, 1435874400 old_id from events_1435874400 union 
    select *, 1436479200 old_id from events_1436479200 union 
    select *, 1437084000 old_id from events_1437084000 union 

    ## more tables ####################

    select *, 1519945200 old_id from events_1519945200 union 
    select *, 1524175200 old_id from events_1524175200 

) t

needful things

In case one needs a nifty regex for creating the selects. For exmple in Notepadd++ use replace with

search:

(events_(\d{10}))\r\n

replace 

select *, \2 old_id from \1 union\r\n

Upvotes: 1

Related Questions