Update Query dilema....
-
I'm trying to write an Update query that will convert all of the State names in my table to their two character abbreviations.
I wrote the following but there has to be a better way to do the rest.
UPDATE RepUpdate
SET State = 'MD' WHERE State = 'Maryland'
-
UPDATE RepUpdate
SET State =
CASE
WHEN 'MD' THEN 'Maryland'
WHEN 'CA' THEN 'blah blah'
ELSE 'Blah blah'
END
-
You should be able to, if you have access, create a table with the state name and the abbreviation and update those records.
CREATE TABLE [tblStateMaster] (
[StateAbbrev] [varchar] (2) NOT NULL ,
[StateName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
update tblData
Set StateName = ST.StateName
From tblData D, tblStateMaster ST
WHERE d.StateAbbrev = ST.Abbrev
-
Joe,
I'd be interested to know why you advise against using UPDATE...FROM. Is it purely for proprietary/portability reasons?
P
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply