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

On Mon, 03 Nov 2003 11:57:18 -0500, Jeff Kowalczyk <jtk(at)yahoo(dot)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