On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
