Re: Help on update that subselects other records in table, uses joins

Jeff,

> UPDATE ordercharges INNER JOIN orders ON
> orders.orderid = ordercharges.orderid
> SET orderchargeasbilled =

You may only UPDATE one table at a time, you can't update a JOIN. So when
selecting from another table to filter or calculate your update, the form is:

UPDATE orderchanges
SET orderchargesbilled = {expression}
FROM orders
WHERE orders.orderid = ordercharges.orderid
AND etc.

Second, your value expression for the update is a subselect which includes a
select on the table and field you are updating! This is a recursive loop and
a very bad idea; gods only know what you'll end up with.

I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere Mortals"
from another author.

--
Josh Berkus
Aglio Database Solutions
San Francisco