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

On Wed, 5 Nov 2003 02:58:28 +0900, Michael Glaesemann
<grzm(at)myrealbox(dot)com> wrote:
>> 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';
>
>I'd like to think I would have gotten to this eventually,
>but I doubt it.

Next time you will. Once you manage to find out that you have to deal
with two disjoint sets of ordercharges ('S&H' and 'SALE'), the rest is
pure text manipulation.

>What I came up with was deleting and reinserting the relevant
>ordercharges rows

This might have unwanted side effects (think ON DELETE CASCADE).

You already have:
> 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';

To transform this into an UPDATE statement (which is not standard SQL,
BTW) we have to do a few easy steps. First, the target table of the
UPDATE operation cannot have an alias.

SELECT
ordercharges.orderchargeid,
ordercharges.orderid,
ordercharges.orderchargecode,
0.065 * oc2.orderchargeasbilled AS orderchargeasbilled
FROM
ordercharges,
ordercharges AS oc2,
orders AS o
WHERE
ordercharges.orderid = o.orderid AND
o.customerinvoiceid = '54321' AND
ordercharges.orderchargecode = 'S&H' AND
ordercharges.orderid = oc2.orderid AND
oc2.orderchargecode = 'SALE';

Second, we don't care about how output expressions are named, so we
remove that alias, too.

SELECT
ordercharges.orderchargeid,
ordercharges.orderid,
ordercharges.orderchargecode,
0.065 * oc2.orderchargeasbilled
FROM
...

Third, Postgres implicitly adds the target table to the FROM clause,
so we move it from the FROM clause to after the command verb, when we
change SELECT to UPDATE.

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

Finally we remove the redundant a=a assignments and get:

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

... which looks and behaves like what I posted before.

Servus
Manfred