T-SQL - create new foreign key relationship from non-normalized data

Question

I am trying to figure out the best Transact-SQL to transform non-relational tables into tables that are more relational, and support foreign keys.

Suppose I have a table FRUIT

    Id  Name        USState

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

etc

I want the States to be their own table with an Id and Name :

INSERT INTO USSTATE (Name) Select DISTINCT USState from FRUIT

Now I have table USSTATE

    Id      Name

    1       Alabama
    2       Alaska

etc.

How do I now update the USState value in table FRUIT to point to the Id of USSTATE recursively?

I can do it State by State

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!!!

etc etc.

, but I want to do it recursively. Thanks for any help?

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

I voted up the answer above mine for giving the T-SQL However, your example data ties one fruit to one state... so you'd be creating a one-to-one relationship. In contrast to your question, your data is already normalized (unless there is duplicated or multiple fruit in the table which you didn't include)

Answer 3

You want a new column with a foreign key to table USState, is that correct?

Start by creating the new column to table FRUIT (call it "USStateId"). You will have to make the column nullable temporarily.

Then create an FK relationship beteen USState.Id and Fruit.USStateId.

Then write a query to update the new column with the correct values:

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

Next, make the new column you added non-nullable.

Finally, remove the USState column from the Fruit table.

Answer 4

Not every database supports JOINs in the UPDATE syntax - the correlated subquery is the most portable approach:

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

Mind that you update the data type for the FRUIT.usstate column from VARCHAR/etc to INT:

ALTER TABLE FRUIT
ALTER COLUMN usstate INT NOT NULL