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

On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann
<grzm(at)myrealbox(dot)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