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

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend