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

On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann
<grzm@myrealbox.com> wrote:
>>> 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

That was only one example.  Here's another one: If the target table is
the referenced table of a foreign key relationship without ON DELETE
CASCADE, the unwanted side effect is that the DELETE fails.

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

That's not the same.  What you mean is described under
add_missing_from in
http://developer.postgresql.org/docs/postgres/runtime-config.html.

In a SELECT you *can* omit the table name from the FROM clause, or you
can even omit the whole FROM clause.

In an UPDATE statement you *have to* omit the target table from the
FROM clause.  OTOH you *can* omit additional tables from the FROM
clause:

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

In this case add_missing_from causes the orders table seen in the
WHERE clause to be added to the FROM clause.  Funny, isn't it?

In my personal opinion this "feature" is dangerous and
add_missing_from should be disabled for every 7.4 installation unless
there are compatibility problems with automatically generated queries.

Servus
 Manfred