update query works as I expect in SQL Server, but not in Postgresql

Question

I have an ETL process that seems to work well in SQL Server, but I am trying to port it to Postgresql and running into something I don't understand.

I create a table called c_production in the first step. I then want to update all the values of c_production.myValue based on a join with a lookup table called previous_type_year. In SQL Server the following code works fine:

update c_production 
    set myValue =  PTY.myValue
FROM c_production CP JOIN previous_type_year PTY
    on CP.StFips = PTY.STFIPS
    AND CP.comcode = PTY.comcode
    AND CP.Year = PTY.YEAR

However if I try this with Postgresql I get the same value over and over for myValue. I really don't grok what it's doing. Any tips that would point me to why these two systems treat this query so differently?

Answer 1

In PostgreSQL and SQL Server they added an extension to the SQL standard by adding the FROM clause and both chose to implement it differently. So you need to be careful when copying your updates from one vendor to another.

See section titled "Updating from Several Sources" from this link. http://www.commandprompt.com/ppbook/x7050

https://sqlserverfast.com/blog/hugo/2008/03/lets-deprecate-update-from/

In your case update the SQL for PostgreSQL to work by doing this:

UPDATE c_production 
    set myValue =  PTY.myValue
FROM previous_type_year PTY
WHERE c_production.StFips  = PTY.STFIPS
  AND c_production.comcode = PTY.comcode
  AND c_production.Year    = PTY.YEAR;

In my personal opinion PostgreSQL did a better job with the extension that is more logical than how SQL Server UPDATE FROM works.