update query confusion

        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