Re: update query confusion

Leave assembliesBatch out of the FROM and just put the condition in the
WHERE. Something like

UPDATE assembliesBatch
FROM assemblies
JOIN .....
WHERE assembliesBatch.AssemblyID = assemblies.assemblyID
AND assembliesBatch.batchID = 5

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding(at)tpchd(dot)org
Phone: (253) 798-3549
Pager: (253) 754-0002

>>> Sim Zacks <sim(at)compulab(dot)co(dot)il> 10/12/04 7:22 AM >>>
Ok. I got it working by adding
"and assembliesBatch.AssembliesBatchID=a.AssembliesBatchID"
to the where clause. This seems a bit awkward sytactically. Is there a
cleaner way of doing it?

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

The following query updated all the rows in the
AssembliesBatch table, not just where batchID=5.

There are 2 rows in the AssembliesBatch table with batch ID of
5 and I wanted to update both of them with their price, based
on the data in the from clause. One row has 105 units and the
other row has 2006 units. the active price in both rows is 6.6
and the pricedifferential is 0. My expectation is that the
first row would be updated to 693 and the second to be updated
to 13239.6. Instead every row in the table was updated to 693.

This syntax works in MS SQL Server to update exactly as I
expected, with the difference that you have to use the
aliasname after the update keyword and postgresql does not
allow that.
If anyone can help, I would greatly appreciate it.

update AssembliesBatch set
BuildPrice=a.units*(coalesce(ActivePrice,0) +
coalesce(PriceDifferential,0))
from AssembliesBatch a join assemblies b on
a.AssemblyID=b.assemblyID
left join qry_AssemblyPrices c on c.AssemblyID=b.assemblyID
left join ProductQuantityPrice d on d.ProductID=b.ProductID
inner join qry_TotalBatchProductCards e on
e.ProductID=b.ProductID and e.BatchID=a.BatchID
and e.TotalCards between minquantity and maxquantity
where a.BatchID=5;

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster