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'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 ;