update script stored procedure sql server2008
I am fairly new to posting questions i greatly appreciate your support. I wanted to write a proc to update my table testAd.
A replace function could do it but I have almost a million records to update and I am trying to use stored procedure.
Could you have a look at what I have started? Thank you I appreciate it. I should come up with something by end of day today..a kind of tough situation. I tried the one below it doesn't seem to be working.
CREATE PROC [dbo].[testAd] AS DECLARE @ci VARCHAR (255) UPDATE Stage.TestAdd SET @ci=AddressOne SET @ci= CASE @ci WHEN 'Des Moines' THEN 'IA' WHEN 'Seattle' THEN 'WA' WHEN 'Pheonix' THEN 'Az' WHEN 'Phx' THEN 'Az' . . ELSE @ci
If I understand correctly you want to update a value stored in one field from [city] to matching [state]
Instead of using a long case statement you can use a table variable with valid pairs and then join with that table in update
something like here:
declare @cityInState as table(city varchar(50), state char(2)) insert into @cityInState values ('Des Moines','IA') ,('Seattle', 'WA') ,('Pheonix','Az') ,('Phx','Az') -- etc ... update yt set fieldToUpdate = cis.state from yourTable yt inner join @cityInState cit on yt.fieldToUpdate = cit.city
WARNING: that will replace the city value with the state, so only run this if that is what you want.
It would be really better to add this as an extra field (ideally a reference to a lookup table) as after the update you will loose the city info.