If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Updating a table from another table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 7
Updating a table from another table

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: INDIA
Posts: 1
Angry

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.
Reply With Quote
  #3 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Re: Updating a table from another table

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'



Quote:
Originally posted by zlek131
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.
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On