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?

Community · Accepted Answer · 2018-11-13 23:20:30Z

3

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.

edited Nov 13, 2018 at 23:20

CommunityBot

11 silver badge

answered Apr 1, 2011 at 19:15

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JD Long Over a year ago

Thanks for both the good reference and the detailed answer. I figured it was something like this, but my google foo was not able to extract a good answer.

Travis Stevens Over a year ago

Quick note. I think you can still alias the table: UPDATE c_production CP set myValue = PTY.myValue FROM previous_type_year PTY WHERE CP.StFips = PTY.STFIPS AND CP.comcode = PTY.comcode AND CP.Year = PTY.YEAR;

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related