Can I retroactively add a foreign key ID column and populate it with the values of another table's primary key without having to enter manually?

Question

I have two tables:

Table1 (TagID, TagName, Primary Key (TagID)<br>
Table2 (TagName,ProductName, ProductID)

I would like to add a TagID column to Table2 and populate it with TagIDs from table1 where TagName=TagName.

Is this possible without going through and individually entering the TagIDs?

Answer 1

Yes, you can do this by altering the table and then updating the values:

alter table table2 add column TagId int;

update table2 t2 join
       table1 t1
       on t2.TagName = t1.TagName
    set t2.TagId = t1.TagId;

alter table table2 add constraint fk_table2_tagid
    foreign key (TagId) references table1(TagId);

I would recommend building an index on table1(TagName) for performance.