MySQL: How to fill new column of existing table with value based on select from another table
mysql, sql
Solution
You want to UPDATE not INSERT:
UPDATE table1 t1
INNER JOIN table2 t2 ON t1.typeName = t2.typename
SET t1.typeID = t2.typeID
But if you are going to have duplicate values on both tables you really should consider have only a foreign key (maybe this typeID) and have the name only in a reference table.
Problem
My knowledge of SQL trully isn't so great to perform what I want. I have 2 tables: One is long data table with many columns, one of them `TypeName`, recently I've added new column to the table `TypeID` (it is empty atm). Like: ``` column1 column2 column3 TypeName TypeID ------------------------------------------------ ... ... ... name1 NULL ... ... ... name2 NULL ... ... ... name1 NULL ... ... ... name3 NULL ... ... ... name1 NULL ... ... ... name4 NULL ... ... ... name5 NULL ``` Second table I call as referenceTable. It has mapping for each possible TypeName to TypeID. Like: ``` TypeID TypeName ------------------- 0 name1 1 name2 2 name3 etc... ``` I want to fill TypeID in data table based correspondent value in refference table. What would be the query for that? EDIT: Yes I want to remove `TypeName` from data table after I fill `TypeID` columns with correct values. And ofcourse I'm looking for the query that do this for whole table at once.