MySQL query for migrating entities into a new table

Question

In our database, we have a few tables in which the entities sometimes have a file appended and thus they have a file column storing the name of that file on disk.

id      entity_name     file

123     document1       fdjie.txt     (from table documents)
456     employee1       null          (from table employees)
789     building1       sgrfe.txt     (from table buildings)

I have created a new table called files into which I need to "copy" all the entities that have the file column filled. Eventually I will drop the file column from all the original tables.

The files table must also have a rel_id column and a table_name for the table it came from.

id      table_name      rel_id      entity_name     file

234     documents       123         document1       fdjie.txt
235     buildings       789         building1       sgrfe.txt

Since 'employee1' has no file, there will of course be no insert for that one.

How can I do this? I tried an insert statement with a subselect but I realized that I need something like a loop. Can I do this in MySQL?

I assume the first table was a result from a joined query between those tables. Please, post the query that generate those and also all table schema with sample data. — FanoFN, Jun 28 '21 at 00:34
No the first sample is not an actual table, it's just my example to illustrate. Sorry that might have been confusing, I shouldn't have put it into backticks as code. — Jakub Jenč, Jun 28 '21 at 01:40

score 0 · Accepted Answer · answered Jun 28 '21 at 01:04

I'm not sure why you'd need a loop, unless you have a very large number of source tables.

Would something like this do what you need:

insert into files (table_name, rel_id, entity_name, file) 
  select * from (
    select 'documents', id, entity_name, file from `documents` where file is not null
     union all
     select 'employees', id, entity_name, file from `employees` where file is not null
     union all
     select 'buildings', id, entity_name, file from `buildings` where file is not null
   ) as a ;

MySQL query for migrating entities into a new table

1 Answers1