Wednesday, March 28, 2012

Replacing a column with a foreign key

In an *existing* database, how would you remove the City column in theEmployees table, and put in the CityID key column from the Cities table?first you would add a cityid column with alter table add column syntax.
Then to add the data, you could do a join with cities table.

UPDATE
Employees
SET
Cityid = C.CityID
FROM
Employees E
INNER JOIN Cities C
ON E.city = C.city

Then, removing the city column should be as simple as alter table remove column.|||

The following simplified version also works:

UPDATE Employees
SET Cityid = C.CityID
FROM Cities C
WHERE Employees.City = C.City

No comments:

Post a Comment