By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

Complex Update Queries with Fromlist

P: n/a
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: md*****@dexterchaney.com

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.