Question on a correlated update

From: Brian M. Biggs <bbiggs_at_cincom.com>
Date: 1996/11/14
Message-ID: <328BB9D6.1279@cincom.com>#1/1

All,

I have the following correlated update on a table:

---

UPDATE
   ctrt
SET
    (tot_bill_amt, tot_bill_tax_amt) =

       (
        SELECT
            NVL(SUM(billing_amt * exchange_rate), 0)
           ,NVL(SUM(tot_tax * exchange_rate), 0)
        FROM
           bmem
        WHERE
            bmem.org_id          = ctrt.org_id
        AND bmem.contract_id     = ctrt.contract_id
        AND bmem.billing_cd      = 'IV'
        AND bmem.status IN ('OP', 'AR', 'CL')
        AND bmem.cncl_bil_rsn_cd = '   '
       )
WHERE
       contract_status <> 'CL'


-------------------------------

This works fine and accomplishes what I want it to do. However, it does assume that I am updating _every_ row in the CTRT table that meets my WHERE condition, even if it has no children in the BMEM table. That's why I had to put in the NVL function.

I used the EXISTS operator by adding the following condition to the WHERE clause in the UPDATE statement, and now it only updates rows in CTRT that have child records in BMEM, and leaves the others alone.

---

   AND EXISTS (SELECT

                  'X'
               FROM
                  bmem
               WHERE
                      org_id = ctrt.org_id
                  AND contract_id = ctrt.contract_id
              )


-------------------------------

This worked, but is there a better way to do it? For some reason, this doesn't seem "clean", but maybe it's just me. Is the EXISTS operator an Oracle specific function, or is that operator present in other relational databases as well?

Any help is appreciated. Please respond to this newsgroup as well as email.

Thanks,
Brian

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/

Received on Thu Nov 14 1996 - 00:00:00 CST