Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    14

    Unanswered: Update from join - SQL Server to DB2 conversion

    Hi,
    Am a DB2 newbie with some existing SQL Server queries , but am having difficulty porting to DB2 9.5. I have a table Tbl1 with fields Key1, Key2,F1, F2 and F3. Key1 and Key2 together form the Primary Key.
    1. I have a field f1_count that I want to fill with the query below:

    update Tbl1 set f1_count = tmp.freq from
    Tbl1 as t1
    inner join
    (select f1, count (*) as freq from Tbl1 group by f1) tmp
    on
    t1.f1=tmp.f1
    2. I have a field f1_flag that I want to update with query below:

    update Tbl1 set f1_flag=x.f1 from
    Tbl1 t3 inner join
    (select distinct f1,key1,key2 from Tbl1 t1 inner join Tbl1 t2 on
    t1.f2=t2.f2 and t1.f3=t2.f3 and t1.f1 <> t2.f1) x
    on
    t3.key1=x.key1 and t3.key2=x.key2

    An additional difficulty I have is if f2 is a CLOB field. Then the expression t1.f2=t2.f2 does not work out.

    TIA
    Karthik

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rao_karthik
    1. I have a field f1_count that I want to fill with the query below:

    update Tbl1 set f1_count = tmp.freq from
    Tbl1 as t1
    inner join
    (select f1, count (*) as freq from Tbl1 group by f1) tmp
    on
    t1.f1=tmp.f1
    I would check the DB2 statement syntax if I were you - it differs from the MS SQL deviations from the SQL standard.

    Code:
    update Tbl1 t1 set f1_count = (select tmp.freq from 
    (select f1, count (*) as freq from Tbl1 group by f1) tmp
    where
    t1.f1=tmp.f1)
    You have correctly stated that you cannot compare two CLOB columns; try SUBSTR() or cast them to VARCHARs.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2008
    Posts
    14

    CLOB Conversion Performance

    Hi,
    Thanks for your reply, will try it out.

    Casting a CLOB to Varchar causes a huge performance drop. What is the best way to compare two CLOB fields?
    TIA
    Kar

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Do you really need CLOBs? If you strings are always shorter than 32K, just stick with VARCHAR.
    Why do you believe it has a huge impact on performance? What did you compare?

    Regarding your UPDATE statement - the syntax is not standard SQL and it doesn't make much sense either.
    update Tbl1 set f1_count = tmp.freq from
    Tbl1 as t1
    inner join
    (select f1, count (*) as freq from Tbl1 group by f1) tmp
    on
    t1.f1=tmp.f1
    It reads: change a value in column "f1_count" and set the new value to some strange expression that is not a valid subselect or something else. Furthermore, if it were a subselect, there is no correlation defined between Tbl1 for the UPDATE and Tbl1 in the subselect. I understand what the statement tries to do - but it is just not logical. What you need instead is this:
    Code:
    UPDATE tbl1 AS o
    SET    f1_count = ( SELECT COUNT(*)
                        FROM tbl1 AS i
                        WHERE o.f1 = i.f1 )
    However, you should think carefully if you want to use such a design. Your table is not properly normalized and each insert/update/delete affecting the values in column "f1" requires you to run the statement again. Using a separate table for the counts, establishing a referential constraint from "tbl1" to this new table and then applying triggers that update the count automatically seems to be a better way.

    Alternatively, you could try something like this:
    Code:
    UPDATE ( SELECT f1_count,
                    ( SELECT COUNT(f1)
                      FROM  tbl1 AS i
                      WHERE i.f1 = o.f1 ) AS count
             FROM tbl1 AS o ) AS t
    SET    f1_count = count
    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
  •