T-SQL - 从非规范化数据创建新的外键关系

Question

我正在尝试找出最佳的 Transact-SQLTransact-SQL，将非关系表转换为关系性更强且支持外键的表。

假设我有一个表 FRUIT，

    Id  Name        USState

    1   Apple       Washington
    2   Pineapple   Hawaii
    3   Orange      Florida
    4   Peach       Georgia

etc

我希望各州成为自己的表，并具有 Id 和名称：

INSERT INTO USSTATE (Name) Select DISTINCT USState from FRUIT

现在我有表 USSTATE

    Id      Name

    1       Alabama
    2       Alaska

etc.

现在如何更新表 FRUIT 中的 USState 值以递归地指向 USSTATE 的 Id？

我可以逐个州

DECLARE @USSTATE nvarchar(100)
Set @USSTATE = 'Alabama'
Update FRUIT Set USState = (SELECT Id from USSTATE where Name like @USSTATE)  
Set @USSTATE = 'Alaska'   -- do this for each State?   Arghh!!!

等

进行，但我想递归地进行。感谢您的帮助？

Answer 1

UPDATE F
SET F.USState = S.ID
FROM FRUID AS F
INNER JOIN USSTATE AS S ON F.USState = S.Name

Answer 2

并非每个数据库都支持 UPDATE 语法中的 JOIN - 相关子查询是最可移植的方法：

UPDATE FRUIT
   SET usstate = (SELECT us.id 
                    FROM USSTATE us
                   WHERE us.name = usstate)

请注意 将 FRUIT.usstate 列的数据类型从 VARCHAR/etc 更新为 INT：

ALTER TABLE FRUIT
ALTER COLUMN usstate INT NOT NULL

Answer 3

我对上面的答案投了赞成票，因为给出了 T-SQL
但是，您的示例数据将一种水果与一种状态联系起来......因此您将创建一对一的关系。
与您的问题相反，您的数据已经标准化（除非表中存在您未包含的重复或多个水果）

Answer 4

您想要一个带有表 USState 外键的新列，对吗？

首先为表 FRUIT 创建新列（将其称为“USStateId”）。您必须暂时将该列设置为空。

然后在USState.Id和Fruit.USStateId之间创建FK关系。

然后编写一个查询以使用正确的值更新新列：

--(untested but should be close)
UPDATE f 
SET f.USStateId = s.Id
FROM Fruit f 
INNER JOIN USState s ON f.USState = s.Name

接下来，使您添加的新列不可为空。

最后，从 Fruit 表中删除 USState 列。