MySQL (SQLite) : insert rows based on a query with multiple rows result

Question

有没有办法根据具有多行结果的查询插入行？

像这样:

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

使用 SQLite3(首选)/MySQL。

谢谢

这是我尝试过的:

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

Answer 1

只是做:

insert into table_B (field_1, field_2)
select brand, date
from table_A
where...

这将在 table_B 中插入从 SELECT 返回的所有行。

在你的情况下你可以改变::

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

到

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%"

尽管这只会在 fk_id_media 中为您提供变量值。 fk_id_tag 将始终相同，但看起来您希望这样。