Multiple update in same table

  • 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

  • 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

     

  • 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?

  • 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

  • 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.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • 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