Обсуждение: 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
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
Sim Zacks <sim@compulab.co.il> writes: > 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; I believe that SQL Server identifies the target table (AssembliesBatch) with "AssembliesBatch a", whereas Postgres does not, turning this into an unconstrained self-join. You need to do something more like update AssembliesBatch set BuildPrice=AssembliesBatch.units*(coalesce(ActivePrice,0) + coalesce(PriceDifferential,0)) from assemblies b 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=AssembliesBatch.BatchID and e.TotalCards between minquantity and maxquantity where AssembliesBatch.AssemblyID=b.assemblyID and AssembliesBatch.BatchID=5; If we supported an alias for the update target table you could write this as update AssembliesBatch a set BuildPrice=a.units*(coalesce(ActivePrice,0) + coalesce(PriceDifferential,0)) from assemblies b 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.AssemblyID=b.assemblyID and a.BatchID=5; which is a bit less typing but not fundamentally different. However, the SQL spec does not allow an alias there and at present we have not decided to extend the spec in this particular direction. regards, tom lane
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@tpchd.org Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Sim Zacks <sim@compulab.co.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