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