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

From Manfred Koizar
Subject Re: Help on update that subselects other records in table, uses joins
Date
Msg-id q37lqv8bfcmlfaas3ddoskic7es5gsbvkq@email.aon.at
Whole thread Raw
In response to Re: Help on update that subselects other records in table, uses joins  (Michael Glaesemann <grzm@myrealbox.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: George Essig
Date:
Subject: Re: tsearch2 and gist index bloat
Next
From: Adam Ruth
Date:
Subject: Re: Insert and obtain the pk