Complex Update Queries with Fromlist

Mark Dexter wrote:

update orders set RequiredDate =
(case when c.city in ('Seattle','Portland') then date(o.OrderDate) + 1
else date(o.OrderDate) + 2 end)
from orders o
join customers c on
o.Customerid = c.Customerid
where c.region in ('WA','OR')
and orders.orderid = o.orderid

The only difference being that I need to add the join at the end to join
the orders table in the update statement with the "orders o" table in
the fromlist.

That's because of the explicit join you're using. The "orders o" in the
FROM clause is different from the "orders" table in the UPDATE clause.

I'd probably use something like:

UPDATE orders
SET RequiredDate = ...
FROM
customers c
WHERE
orders.Customerid = c.Customerid
AND c.region in (...)

First, does this look correct? It appears to work the way I want.
Second, it would be really nice if there was better documentation of the
UPDATE statement in Postgres, including examples of this type.

Patches to the documentation are always gratefully received. The latest
version of the documentation is available on the main website (follow
the developers link). Contributions to the docs mailing-list in plain
text are generally fine.

--
Richard Huxton
Archonet Ltd

Thank you. That works for the simple example I had.

However, I have an additional question about this. What if I need to do
a LEFT OUTER JOIN in the Fromlist. For example, this query works but it
requires the orders table to appear twice.

UPDATE orders set requireddate =
(case when c.city in ('Seattle','Portland') then date(o.OrderDate) + 1

else date(o.OrderDate) + 2 end)
FROM orders o
LEFT OUTER JOIN customers c on
o.customerid = c.customerid
where c.region in ('WA','OR')
and orders.orderid = o.orderid

Is there some way to do an outer join in the Fromlist back to the main
table being updated without having it appear twice? Thanks.