Results 1 to 5 of 5

Thread: Update Query

  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: Update Query

    Hi,
    I have the following Update Query. Is there any better way of writing this query ?

    Stats: Table2 is a subset of Table1
    Both Tables has a unique index on cold and cole

    UPDATE TABLE1 A
    SET (COLA,COLB) =(SELECT B.COLA,B.COLB
    FROM TABLE2 B
    WHERE B.COLD = A.COLD
    AND B.COLDE = A.COLE
    AND (B.COLA != A.COLa
    OR B.COLB != A.COLB)
    )
    WHERE (A.COLD,A.COLE) IN (SELECT COLD,COLE FROM TABLE2);

    Thanks,
    Rajesh

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could try a MERGE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could try a MERGE statement. However, your predicates in the 2 subselects are not the same. So you may actually set SQL NULL for some of the rows in TABLE1.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2007
    Posts
    27
    Thats right....So just wanted to know...if there any other way of doing it...
    we can do the positioned update...but that will be expensive given the table size is around 200k..

    THanks

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As I said, the MERGE statement is probably simpler to write than repeating the same subselect in the SET clause and the WHERE clause.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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