update query confusion

[Sim Zacks]

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 pricedifferenti al 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.un its*(coalesce(A ctivePrice,0) + coalesce(PriceD ifferential,0))
from AssembliesBatch a join assemblies b on a.AssemblyID=b. assemblyID
left join qry_AssemblyPri ces c on c.AssemblyID=b. assemblyID
left join ProductQuantity Price d on d.ProductID=b.P roductID
inner join qry_TotalBatchP roductCards e on e.ProductID=b.P roductID and e.BatchID=a.Bat chID
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]

Re: update query confusion

Ok. I got it working by adding
"and assembliesBatch .AssembliesBatc hID=a.Assemblie sBatchID"
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 pricedifferenti al 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.un its*(coalesce(A ctivePrice,0) + coalesce(PriceD ifferential,0))
from AssembliesBatch a join assemblies b on a.AssemblyID=b. assemblyID
left join qry_AssemblyPri ces c on c.AssemblyID=b. assemblyID
left join ProductQuantity Price d on d.ProductID=b.P roductID
inner join qry_TotalBatchP roductCards e on e.ProductID=b.P roductID and e.BatchID=a.Bat chID
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

[Tom Lane]

Re: update query confusion

Sim Zacks <sim@compulab.c o.il> writes:[color=blue]
> 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.[/color]
[color=blue]
> update AssembliesBatch set BuildPrice=a.un its*(coalesce(A ctivePrice,0) + coalesce(PriceD ifferential,0))
> from AssembliesBatch a join assemblies b on a.AssemblyID=b. assemblyID
> left join qry_AssemblyPri ces c on c.AssemblyID=b. assemblyID
> left join ProductQuantity Price d on d.ProductID=b.P roductID
> inner join qry_TotalBatchP roductCards e on e.ProductID=b.P roductID and e.BatchID=a.Bat chID
> and e.TotalCards between minquantity and maxquantity
> where a.BatchID=5;[/color]

I believe that SQL Server identifies the target table (AssembliesBatc h)
with "AssembliesBatc h a", whereas Postgres does not, turning this into
an unconstrained self-join. You need to do something more like

update AssembliesBatch set BuildPrice=Asse mbliesBatch.uni ts*(coalesce(Ac tivePrice,0) + coalesce(PriceD ifferential,0))
from assemblies b
left join qry_AssemblyPri ces c on c.AssemblyID=b. assemblyID
left join ProductQuantity Price d on d.ProductID=b.P roductID
inner join qry_TotalBatchP roductCards e on e.ProductID=b.P roductID and e.BatchID=Assem bliesBatch.Batc hID
and e.TotalCards between minquantity and maxquantity
where AssembliesBatch .AssemblyID=b.a ssemblyID
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.un its*(coalesce(A ctivePrice,0) + coalesce(PriceD ifferential,0))
from assemblies b
left join qry_AssemblyPri ces c on c.AssemblyID=b. assemblyID
left join ProductQuantity Price d on d.ProductID=b.P roductID
inner join qry_TotalBatchP roductCards e on e.ProductID=b.P roductID and e.BatchID=a.Bat chID
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)