Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    10

    Unanswered: UPDATE with subquery

    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;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Include your <expr> into the subquery, such as
    Code:
    UPDATE callpayments cp
       SET cp.cost = (SELECT cp.units + md.duration
                        FROM mindurationlog md
                       WHERE ...);

Posting Permissions

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