UPDATE with subquery

bengolden · #1 (**permalink**) 10-01-04, 05:53

Hi,

Oracle documentation tells me that the syntax for the UPDATE statement is:

Code:

UPDATE tablename
   SET field = expr
 WHERE condition

or

Code:

UPDATE tablename
   SET field = (subquery)
 WHERE condition

I want to combine the two like this:

Code:

UPDATE tablename
   SET field = expr + (subquery)
 WHERE condition

but I get PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: when I try to compile.

Does anybody know a way round this? The actual UPDATE statement i'm trying to use is:

Code:

      UPDATE CallPayments CP
         SET CP.Cost = (((CP.Units + (SELECT SUM(MD.Duration * MD.ItemCount)  
                                        FROM MinDurationLog MD 
                                       WHERE MD.CallPaymentFK = CP.CallPaymentPK 
                                         AND MD.Duration < vRates.MinUnits)) / 60) * vRates.UnitCharge)
                       + (CP.Items * vRates.ItemCharge)
                       + -1 * (SELECT SUM((GREATEST(MD.Duration, vRates.MinUnits) * MD.ItemCount) / 60 
                                           * vRates.UnitCharge + MD.ItemCount * vRates.ItemCharge)
                                 FROM MinDurationLog MD 
                                WHERE MD.CallPaymentFK = CP.CallPaymentPK 
                                  AND (GREATEST(MD.Duration, vRates.MinUnits) * MD.ItemCount) / 60 
                                       * vRates.UnitCharge + MD.ItemCount * vRates.ItemCharge < vRates.MinCharge * MD.ItemCount)
                       +      (SELECT SUM(MD.ItemCount * vRates.MinCharge)
                                 FROM MinDurationLog MD 
                                WHERE MD.CallPaymentFK = CP.CallPaymentPK 
                                  AND (GREATEST(MD.Duration, vRates.MinUnits) * MD.ItemCount) / 60 
                                       * vRates.UnitCharge + MD.ItemCount * vRates.ItemCharge < vRates.MinCharge * MD.ItemCount)
       WHERE CP.SupplierFK = vSupplierFK
         AND CP.TimeBandFK = vRates.TimeBandFK
         AND CP.DDICat = vDDICat AND CP.DDISubCat = vDDISubCat
         AND CP.CLICat = vCLICat AND CP.CLISubCat = vCLISubCat
         AND CP.TLICat = vTLICat AND CP.TLISubCat = vTLISubCat
         AND CP.DateStamp = vProcessDate;

Littlefoot · #2 (**permalink**) 10-01-04, 06:23

Include your <expr> into the subquery, such as

Code:

UPDATE callpayments cp
   SET cp.cost = (SELECT cp.units + md.duration
                    FROM mindurationlog md
                   WHERE ...);