Update from join - SQL Server to DB2 conversion

rao_karthik · #1 (**permalink**) 07-16-08, 09:21

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

n_i · #2 (**permalink**) 07-16-08, 09:49

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.

rao_karthik · #3 (**permalink**) 07-18-08, 04:09

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

stolze · #4 (**permalink**) 07-18-08, 06:34

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.

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech