Is there a way to insert rows based on a query with multiple rows result?
Something like this:
For each row in (select brand, date from table_A where ...) insert into table_B (field_1, field_2) VALUES (table_A.brand, table_A.date);
Using SQLite3 (preferred) / MySQL.
Thanks
Here's what I've tried:
insert into media_tags (fk_id_media, fk_id_tag) VALUES ( (select id_media from media where fullpath like "%C32%") , (select id_tag from tags where tagname='digital') )
Just do:
insert into table_B (field_1, field_2)
select brand, date
from table_A
where...
This will insert in table_B all the rows that are returned from the SELECT.
In your case you could change::
insert into media_tags (fk_id_media, fk_id_tag)
values (
(
select id_media
from media
where fullpath like "%C32%"
), (
select id_tag
from tags
where tagname = 'digital'
)
)
to
insert into media_tags (fk_id_media, fk_id_tag)
select id_media, (
select id_tag
from tags
where tagname = 'digital'
)
from media
where fullpath like "%C32%"
This will although only give you variable values in fk_id_media. fk_id_tag will always be the same, but it looks that you want it that way.
INSERT .. Values() lets you insert only one row at a time, to make it work for multiple rows this is the way you do it.