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

On Tuesday, November 4, 2003, at 05:45 AM, Manfred Koizar wrote:

> 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';

Nicely done. I'd like to think I would have gotten to this eventually,
but I doubt it. I was definitely on to the two instances of
ordercharges, but I think what kept me from arriving at this was that I
didn't know how to refer to the target table in the WHERE clause.
Please correct me if I'm wrong, but the ordercharges.* in the WHERE
clause is the target ordercharges, right? Really interesting!

What I came up with was deleting and reinserting the relevant
ordercharges rows inside a transaction:

BEGIN;
CREATE TEMPORARY TABLE ordercharges_temp AS
SELECT
oc.orderchargeid,
oc.orderid,
oc.orderchargecode,
0.065 * oc2.orderchargeasbilled AS orderchargeasbilled
FROM
ordercharges AS oc,
ordercharges AS oc2,
orders AS o
WHERE
oc.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
oc.orderchargecode = 'S&H' AND
oc.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';
DELETE FROM ordercharges WHERE
orderchargeid IN (SELECT orderchargeid FROM ordercharges_temp);
INSERT INTO ordercharges
SELECT * FROM ordercharges_temp;
COMMIT;

I think yours is much more elegant, Manfred. Thanks for providing this
solution!
Well, Jeff, if you're interested in having another (albeit longer)
option, here you go. :P

Regards,
Michael
grzm myrealbox com