Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Updating table value where 0 set 100 and vice versa

Answered Updating table value where 0 set 100 and vice versa

  • Friday, January 18, 2013 1:46 PM
     
     
    Sign In to Vote
    0

    Hi,

    I have a table say TestTable with only one column say myID  it is having data like this:

    MyID

    0

    954

    0

    0

    100

    100

    256

    0

    100

    100

    0

     

    What I want is.....    
    Wherever the 0 (Zero) is there update with 100 and wherever it is 100 update with 0

    Means the result should be like this

     

    MyID

    100

    954

    100

    100

    0

    0

    256

    100

    0

    0

    100

     

    Conditions:    1> we should not add any column to the table
                          2> In a single shot this should be done
                          3> No multiple update statement

    Note: Please dont say that this question is not ideal and useless as I got requirement from a world leading company having requirement like this and they have the solution also...

    thanks in advance !!!!

     

All Replies

  • Friday, January 18, 2013 1:55 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hello Rahul,

    You can solve it with a simple CASE WHEN condition:

    UPDATE myTable
SET MyID = CASE WHEN MyId = 0 THEN 100
                WHEN MyID = 100 THEN 0
                ELSE MyID END
WHERE MyID IN (0, 100)

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 1:56 PM
     
     Answered
    Sign In to Vote
    1

    Hi,

    This should do the job:

    Update TestTable

    Set myID = Case when myID = 0 then 100 

        when myID = 100 then 0 end

    Where myID in (0, 100)

    Regards,

    Matan

     
  • Friday, January 18, 2013 2:06 PM
     
     
    Sign In to Vote
    0

    Hi Olaf,

    thanks for your reply....I was trying this from last 1 hour but neve clicked about case statement.
    After posting the question suddenly it clicked my mind and i got the same answer what you have replied with ...:)

    update Table
    set myid =
    (
    case 
     when myid = 0 then 100
     when myid = 100 then 0
     else myid
     end
    )

    ONce again thanks !!!

    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com