CODEWITHSUNDEEP
mysqlselectinsertsqlite
Azevedo
Azevedo

Reputation: 2189

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

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

Upvotes: 2

Views: 221

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

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.

Upvotes: 3

Related Questions