AS/400 selective update with SQL

20 pts.
Tags:
AS/400
SQL
SQL statements
Hi All, I am trying to create an SQL statement that will update many records based on a file containing fewer records. I can easily update ALL records in the primary file but I really only want to update those that need to be updated. In the item master file the cost has been changed for some records. In the warehouse file I need to update those records that now have a cost that is different from the item master record. This SQL statement will update all records with no problem:
UPDATE warehouse a
set a.w_cost = (SELECT distinct b.m_cost FROM itemmast b)
where (a.w_item = b.m_item and a.w_comp = b.m_comp)
(It is a guarantee that there will be an item record in the master file to match the multiple item records in the warehouse file.) I haven't been able to figure out how to add in the constraint that the update should only occur if the W_COST does not equal the M_COST. I've played with both SQLRPGLE and interactive SQL without success. Thanks for any help! Carolyn


Software/Hardware used:
iSeries V7R1
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    An initial observation: I don't think your code works or even compiles/run. Your WHERE clause's conditions involve the itemmast table, but from the main query's point of view it doesn't exist, since it only exists inside the subquery.

    Anyway,  something like this could work:

    UPDATE warehouse a
    set a.w_cost = 
      (SELECT distinct b.m_cost FROM itemmast b
            WHERE b.m_item = a.w_item
              and b.m_comp = a.w_comp)
    WHERE EXISTS 
      (SELECT 1 FROM itemmast c
            WHERE a.w_item = c.m_item 
              and a.w_comp = c.m_comp
              and a.w_cost != c.m_cost)

    84,745 pointsBadges:
    report
  • Ckelly01

    Thank you, that does exactly what I wanted. 

    I should note however that the code as I posted it does work both in an SQLRPGLE program and from an interactive SQL session.  It simply updaates every record in the file.

    20 pointsBadges:
    report
  • carlosdl
    Glad it helped.

    Thanks for posting back.
    84,745 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: