locked
Updating table value where 0 set 100 and vice versa RRS feed

  • Question

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

    Friday, January 18, 2013 1:46 PM

Answers

  • 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 1:56 PM
  • 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:55 PM

All replies

  • 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:55 PM
  • 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 1:56 PM
  • 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

    Friday, January 18, 2013 2:06 PM