Re: update query confusion

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