2

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.

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
user2997497
  • 159
  • 1
  • 2
  • 11
  • Please tell us what you have tried. – Jonathan Hall Nov 25 '13 at 20:57
  • Have you tried UPDATE ... SELECT ... or read other posts http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query ? – Noam Rathaus Nov 25 '13 at 20:58
  • I'v eonly tried to come up with UPDATE ... SELECT ... query but my knowlede did allow me only to to it for single row. But how to do such thing for whole table i have no idea. BTW not sure that my UPDATE ... SELECT ... thingy would be corect in first place. – user2997497 Nov 25 '13 at 21:21

1 Answers1

12

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.

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • My intention is to remove TypeName from data table after I populate TypeID with correct values. If so can I do the query you suggested? – user2997497 Nov 25 '13 at 21:09
  • Yes, but make sure you do the query first and remove it after. – Filipe Silva Nov 25 '13 at 21:22
  • The one above didn't work in sql server. So i ended up with something like below. Just in case someone will need. `UPDATE t1 SET t1.Column = t2.SomeColumn FROM tableToUpdate t1, table2 t2 WHERE t2.Id = t1.SomeId ` – Elnoor Feb 26 '18 at 17:41