sql - 用外键将列中的数据替换为其他表中的相同数据

Question

希望你们能帮我弄清楚如何做一些我以前没有遇到过的事情。

我有一张表，称为 TableA，有很多列。其中之一是“状态”，其中包含以下条目:

state
'MA'
'NJ'
'HI'

等等。我想创建某种方式将这些值提取到一个新的外键表中，称为 StateTable，它包含 state_key 和 state 列。

例如，这是我如何设想它逐行进行:

第 1 行:表 A 中“状态”列中的值为“MA”。检查 StateTable 是否有一个条目，其中 'state' 列是 'MA'。如果是这样，获取该行的 state_key，并用外键替换 TableA 中的条目，以便该行现在具有到 'MA' 的 FK，而不是直接存储该值。如果 StateTable 没有“MA”条目，则插入它，并对新的 FK 执行相同操作。依此类推，对于每一行。

所以最终结果将是两个表:

表A

state
1
2
3

状态表

state_key    state
1            'MA'
2            'NJ'
3            'HI'

不应该有任何硬编码的东西，因为我也需要对其他列执行此操作，例如 state，它们的有限值数量很少。

tl; dr 一种在将列转换为 FK 表的同时保留列中数据的方法。

有任何想法吗？谢谢!!

Answer 1

创建带有自动枚举字段“state_key”的状态表，使用 SELECT DISTINCT ... INTO填充它，并使用类似的东西

UPDATE TableA SET TableA.state=
   (SELECT state_key FROM StateTable where TableA.state=StateTable.state)

将值放入其中。