Having problems trying to update one table from another....

here is something that works in sybase but not in db2....

update active.dcclm c
set (c.dup_clm_resp_fi = t.new_resp_fi,
c.dup_clm_resp_cont_nbr = t.new_resp_cont_nbr,
c.dup_clm_mass_chg_lvl = v_min_lvl,
c.user_def_code = '' )
from load.dcfichg_ids t, active.dcclm c
where t.dup_clm_mass_chg_lvl = v_min_lvl
and t.dup_clm_set_cntl_nbr = c.dup_clm_set_cntl_nbr
and t.hcsr_icn = c.hcsr_icn
and t.hcsr_time = c.hcsr_time
and t.hcsr_suffix = c.hcsr_suffix;

The load.dcfichg_ids is the "driver" table...with the same key structure as the active.dcclm table....

We tried several veriations with mixed results. any help would be great!!!!


Thanks, Matt.

The Quest is not clear


You have already a table with records, i think

So u want to copy these records in to another table, Is is right

There is a query in Oracle; we can do create a table and simultaneously we can update records from the particular table to this..



Do u need the same.

Please be aware, if something works on a database product, it is not necessary it will work on the other (would certainly be great if they do ) ..

Did you have a chance to look at the SQL Reference at all ?

I don't know what version or what platform of DB2 you are on ... But, here is an example from SQL Reference:

UPDATE EMPLOYEE EU
SET (EU.SALARY, EU.COMM)
=
(SELECT AVG(ES.SALARY), AVG(ES.COMM)
FROM EMPLOYEE ES
WHERE ES.WORKDEPT = EU.WORKDEPT)
WHERE EU.EMPNO = '000120'