Replace data in column with foreign key to same data in other table
foreign-keys, sql
Solution
Create the state table with an auto-enumeration field "state_key", use `SELECT DISTINCT ... INTO` to fill it, and use something like
UPDATE TableA SET TableA.state=
(SELECT state_key FROM StateTable where TableA.state=StateTable.state)
to get the values into it.
Problem
Hopefully you guys can help me figure out how to do something I haven't come across before. I have a table, call it TableA, with many columns. One of them is 'state', with entries such as: ``` state 'MA' 'NJ' 'HI' ``` and so forth. I would like to create some sort of way to pull these values out into a new foreign-keyed table, call it StateTable, which would have columns state_key and state. For example, here's how I envision it proceeding row-by-row: Row 1: Value in 'state' column in TableA is 'MA'. Check to see if StateTable has an entry where the 'state' column is 'MA'. If so, get the state_key for that row, and replace the entry in TableA with the foreign key, so that row now has a FK to 'MA' instead of storing that value directly. If StateTable has no 'MA' entry, insert it, and do the same with the new FK. And so on, for each row. So the end result would be two tables: TableA ``` state 1 2 3 ``` StateTable ``` state_key state 1 'MA' 2 'NJ' 3 'HI' ``` There shouldn't be any hard-coded stuffs going on because I'll need to do this for other columns as well, like state, that have a somewhat small number of finite values. tl;dr a way to preserve data in a column while turning the column into a FK'd table. Any ideas? Thanks!!