Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Location
    Canada
    Posts
    14

    Unanswered: Update table with multiple rows of data

    update actualhrs_staging
    set hcd = (select hcdrate from (select hcdrate, effectivedate,ratename from rate
    where ratename in(select ratename from res_suppcode_map
    where hibssuppliercode in(select hibssuppliercode from actualhrs_staging
    where loaddate = '2012-05-16'))) as table1
    where effectivedate = (select max(effectivedate) from (SELECT ratename,hcdrate, effectivedate
    FROM rate r
    WHERE effectivedate =
    (
    SELECT MAX (effectivedate)
    FROM rate
    WHERE ratename = r.ratename
    )
    group by ratename,hcdrate,effectivedate
    ORDER BY ratename) as table2
    where table1.ratename = table2.ratename
    group by ratename, effectivedate)
    group by hcdrate, ratename, effectivedate
    )
    where loaddate = '2012-05-16'
    I am trying to update the hcd column with rates value from another table but the query is returning multiple rows which db2 is not allowing.

    So, i need a inner join somewhere so to update only required fiels with required values.

    inner join on siteteam actualhrs.siteteamid = siteteam.siteteamid
    inner join on table2siteteam.ratename = table2.ratename


    Thanks
    jassi
    Last edited by jassi; 05-23-12 at 18:03.

  2. #2
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    inner join siteteam st on table2.ratename = st.ratename
    inner join actualhrs_staging ahs on st.siteteamid = ahs.siteteamid

    this is what need to be added to above query.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all,

    correlate your subqueries with a table to be updated(target table) by adding correlation clause, like...
    Code:
    update actualhrs_staging ahs
    ...
    and use the correlation name in subquery rather than repeating target table and joining it in the subquery.
    Code:
    update actualhrs_staging ahs
    ...
    inner join siteteam st on table2.ratename = st.ratename
    ...
     WHERE st.siteteamid = ahs.siteteamid
    ...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that the following example might be a starting point.

    But, the relationships between siteteam, res_suppcode_map, and actualhrs_staging are not clear for me.
    For example:
    It seems that both of res_suppcode_map and siteteam have ratename.
    How to relate them? Or, is it not neccesary to relate them?

    So, I might be off the point.

    Code:
    UPDATE actualhrs_staging ahs
       SET hcd
         = (SELECT hcdrate
             FROM
                   rate             r
             INNER JOIN
                   res_suppcode_map rsm
              ON   r  .ratename         = rsm.ratename
             WHERE rsm.hibssuppliercode = ahs.hibssuppliercode
             ORDER BY
                   r.effectivedate DESC
             FETCH FIRST 1 ROW ONLY
           )
     WHERE loaddate = '2012-05-16'  
    ;

  5. #5
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    thanks tonkuma that's helped a lot

Posting Permissions

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