Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    104

    Unanswered: Question on update in SQL

    Hi

    DB2 8.2.6 on windows 2000

    I have a table T1 with following columns

    KEY
    COL1
    COL2

    Would like to perform the following task using a single SQL statement:

    when KEY=1 and COL2='A' update COL1='X'
    when KEY=1 and COL2 != 'A' update COL1 = 'X' and COL2= 'A'


    is it possible to have a single SQL statement doing the above?

    Thanks in advance

    Hari kumar

  2. #2
    Join Date
    May 2003
    Posts
    113
    does db2/luw have CASE-WHEN expression? that may work in your case.

    Quote Originally Posted by sundaram
    Hi

    DB2 8.2.6 on windows 2000

    I have a table T1 with following columns

    KEY
    COL1
    COL2

    Would like to perform the following task using a single SQL statement:

    when KEY=1 and COL2='A' update COL1='X'
    when KEY=1 and COL2 != 'A' update COL1 = 'X' and COL2= 'A'


    is it possible to have a single SQL statement doing the above?

    Thanks in advance

    Hari kumar

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    UPDATE T1
    SET COL1 = 'X' , COL2= 'A'
    WHERE KEY=1 ;

    if COL2 is already 'A' , this will be recognized by DB2 and no change is done to that column so there is no need for extra code.


    The other alternative is ( as nidm already mentioned ):

    UPDATE T1
    SET COL1 = 'X' ,
    COL2 = CASE WHEN COL2='A' THEN COL2 ELSE 'A' END
    WHERE KEY=1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •