Re: 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