Re: Help on update that subselects other records in table, uses joins - Mailing list pgsql-general

On Wed, 5 Nov 2003 02:58:28 +0900, Michael Glaesemann
<grzm@myrealbox.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