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

END

Answers


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.


Need Your Help

Traversing Google searches using LuaSocket

lua luasocket

I am trying to make a Lua program that creates a google search, and formats all of the results in a different arrangement. The only problem is that I cannot find a way to do these:

Rexport all features from one module to another - node.js

javascript node.js coffeescript

How to export all features of one module via another. Something like in the pseudocode below:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.