Results 1 to 3 of 3
  1. #1
    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.

  2. #2
    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.

  3. #3
    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'



    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.

Posting Permissions

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