Обсуждение: Complex Update Queries with Fromlist
In Microsoft SQL Server, I can write an UPDATE query as follows:
update orders set RequiredDate =
(case when c.City IN ('Seattle','Portland') then o.OrderDate + 2 else o.OrderDate + 1 end)
from orders o
join customers c on
o.Customerid = c.Customerid
where c.region in ('WA','OR')
This query finds 47 rows matching the WHERE clause and updates the RequiredDate in the Orders table based on data in the orders table and the customer table for these 47 rows.
It appears that I can do the same thing in Postgres with the following syntax:
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.
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.
Thanks.
Mark Dexter
Dexter + Chaney
9700 Lake City Way NE, Seattle, WA 98115-2347
Direct Phone: 206.777.6819 Fax: 206-367-9613
General Phone: 800-875-1400
Email: mdexter@dexterchaney.com
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.