Re: Help on update that subselects other records in table, uses joins

On Mon, 03 Nov 2003 11:57:18 -0500, Jeff Kowalczyk <jtk@yahoo.com>
wrote:
>Thanks for the suggestions everyone, however I'm still at the same
>underlying stopping point: the subselect in the SET clause returns
>multiple rows, and I don't know how to make it 'iterate' on each orderid
>in the specified customerinvoiceid without using a JOIN, which is itself
>apparently either not directly possible or complex.
>
>UPDATE ordercharges
>SET orderchargeasbilled = (expression)
>WHERE
>  ordercharges.orderchargecode = 'S&H' and
>  ordercharges.orderid=(SELECT orderid   (tried IN(SELECT...) as well)
>     FROM orders
>     WHERE customerinvoiceid = '54321');
>
>'expression' needs to get the orderchargeasbilled for the current orderid

The key point is that you have to deal with two instances of the
ordercharges table, one having orderchargecode = 'S&H' (this is the
one you want to update), the other one having orderchargecode = 'SALE'
which is where the values come from.

UPDATE ordercharges
   SET orderchargeasbilled = 0.065 * sale.orderchargeasbilled
  FROM orders AS o, ordercharges AS sale
 WHERE ordercharges.orderchargecode = 'S&H'
   AND ordercharges.orderid = o.orderid
   AND sale.orderchargecode = 'SALE'
   AND sale.orderid = o.orderid
   AND o.customerinvoiceid = '54321';

HTH.
Servus
 Manfred