Help to Write a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help to Write a Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-20 : 08:25:09

Manish Batra writes "Hi,
I want to update a table correspoding to another table. I have a table city in which cityid(pk) and cityname is available. another table flight in which FLightid(pk) fromCity and ToCity is available. The field fromcity and tocity displayed the list of city but I want to update the table flight in which fromcity and tocity should display the code of the city corresponding to table city. Pls help to write a query to update the table FLight.

Hope to hear from you soon.
Thanks
With Regards
Manish Batra"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-20 : 08:34:56

You'll need to do this in steps:

--Add columns to hold the City IDs
ALTER TABLE Flight ADD FromCityID INT
ALTER TABLE Flight ADD ToCityID INT
GO

UPDATE Flight
SET FromCityID = CityName
FROM Flight INNER JOIN City
ON Flight.FromCity = City.CityName
GO

UPDATE Flight
SET ToCityID = CityName
FROM Flight INNER JOIN City
ON Flight.ToCity = City.CityName
GO

--Optionally drop the city name columns
ALTER TABLE Flight DROP column FromCity
ALTER TABLE Flight DROP column ToCity
GO

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources