Inserting a row in one table joining on another

Question

I'm not sure how this works, but there are two tables. One table instrument, and another instrumentmetadata. There should only be one row of instrumentmetadata for the instrument. I'm trying to create a migration to remove the current instrumentmetadata for some of the tools since someone added fields to make it 1 to many. I did that with this:

delete  from instrumentmetadata where Id  IN (select MetadataId from instrument)

Now I want to add a row in instrumentmetadata for some of the rows in instrument (create my 1 to 1 metadata for each instrument). I wasn't sure how to do that since my instrumentmetadata's Id field needs to match the MetadataId in the instrument table. We are using fluent-migrator if there is syntax that helps there, but I'm fine with pure sql since I don't really know much about fluent-migrator. Thanks in advance!

Answer 1

Assuming you know the name of the instrument, but not it's ID, you could query the ID like:

insert  instrumentmetadata 
        (id, col1, col2)
select  id
,       'val1'
,       'val2'
from    instrument
where   name = 'Impulse drive replicator'