CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
user2169250
user2169250

Reputation: 1

Update more records with stored procedure

I have the following records imported from excel to the database. The record count is 5,00,000.

EmpId Name City CityId

1 Ramesh LA ?

2 Kumar NewYork ?

I need to fetch the CityId from other table and insert into this CityId

The other table has the alias name for the cities and the cityId

CityId AliasName

1 LA

1 LosAngels

1 Los Angels

1 LA(USA)

I would like to call a stored procedure to update all 500000 records, as functions cannot be used for UPDATING record.

I need CityId field to be updated for each employee from Alias Table

Upvotes: 0

Views: 120

Answers (4)

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

The following query will update the required data:

UPDATE Employees SET CityId = ISNULL((Select CityId FROM Cities where AliasName = City),0)

Upvotes: 0

Kami
Kami

Reputation: 19437

Would it not be easier to set the City as a foreign key rather than adding a redundant data column.

To answer your question, you can achieve this by running the following query. You can also make this into a stored procedure if it needs to be run occasionally.

UPDATE Employees SET CityId = (Select CityId FROM Cities where AliasName = City)

Upvotes: 0

Saksham
Saksham

Reputation: 9380

Hope this helps you out.

UPDATE UserDetails
SET UserDetails.CityID = City.ID
FROM City
WHERE City.AliasName = UserDetails.City

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

you can do something like :

     update employee
     set cityid = b.cityid
     from employee as a inner join city as b on a.city = b.aliasname

Upvotes: 1

Related Questions