May 2, 2007 at 3:53 pm
Hi there,
I need to update a lot of values in a table like this,
Update
Mydatabase.dbo.MyTable
Set
nation1 = '2'
Where
nation1 = '7A'
or like this
Update Mydatabase.dbo.MyTable
Set
nation1 = '1'
Where
nation1 = '2A'
actually I have to do it 39 times on 10 different tables, how do it update multiple values in one query and changing 7A to 2 and 2A to 1 ect. in one query?
Thanks in advance
joejoe
May 3, 2007 at 1:55 am
Something like this?
declare
@t table(id int identity(1,1), nation1 varchar(3))
insert
into @t values('1A')
insert into @t values('2A')
insert into @t values('3A')
insert into @t values('5A')
insert into @t values('7A')
insert into @t values('11A')
select
* from @t
update
@t
set nation1 =
case nation1
when '2A' then '1'
when '7A' then '2'
end
where nation1 in ('2A', '7A')
select
* from @t
Jon
May 3, 2007 at 1:00 pm
Thanks Jon,
I have run the script and SQL Server comes up with the following message
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '’'.
Line 27 is = when '2A' then '1'
Any sugestions?
May 3, 2007 at 5:23 pm
I now realize that "The only caveat is that expressions used within the CASE statement must be of compatible data types" - which means that I can't use CASE to update a '1A' value to '7'
joejoe
May 4, 2007 at 7:08 am
Use a 2 dimensional temp table, or table variable (better), having the old and new values. Then perform an update using an inner join operation.
May 4, 2007 at 8:23 am
Thanks for your advice JohnG, you saved my day !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply