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

Manfred,

Thanks for taking the time to walk me through this. You've pointed out
things I've noticed when writing queries and wondered about. (More
specific comments—and commentary—below.

>> What I came up with was deleting and reinserting the relevant
>> ordercharges rows
>
> This might have unwanted side effects (think ON DELETE CASCADE).

Good point. At this stage in my PostgreSQL progress, I haven't been
using ON DELETE CASCADE because I like to be warned of possible
deletions. But I can definitely see how it could be very useful in
situations where you've thought things through much more than I have.
Since I haven't been confident enough in my skill to think through all
the ramifications, I've been consciously limiting myself. But someday.
Someday these shackles will fall free! :)

> 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)

Bless those PostgreSQL developers who have implemented methods and
means beyond those of SQL! This does seem promising.

>  First, the target table of the UPDATE operation cannot have an alias.
<snip/>
> Second, we don't care about how output expressions are named, so we
> remove that alias, too.
<snip/>
> 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.

I've noticed in SELECT queries when I've neglected to include a table
in the FROM clause but named it specifically in an attribute that
PostgreSQL's added it. I've gone in an added it, because often I
haven't been paying proper attention when writing the query (resulting
in omitting the table from the FROM clause) and end up getting a result
that doesn't join as I want it to, since I haven't accounted for it
fully in the WHERE clause either, e.g., giving variations of each row
for each possible value in the omitted table. Especially nasty when no
column from the omitted but implicitly added table is targeted and I've
got apparently duplicated rows in my beautiful normalized tables! I've
wondered why PostgreSQL let's me make this "mistake", but now I can see
definite benefits.

>      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.

Wow. Not only smart, but able to clearly explain the route from a to b.
Virtual tipple of choice to you, Manfred. If you're ever in Tokyo,
please let me buy you a drink!

Thanks again!

Michael
grzm myrealbox com