PostgreSQL update not working as expected

Question

With 20+ years of experience with MS Access and SQL Server, I'm not a novice with respect to SQL, but I am new to PostgreSQL and I have encountered an issue that makes me feel like a complete noob. I have a simple UPDATE query in which I want to update the destination table d with data from the source View m:

UPDATE chgman.tc_data 
SET reporttime = m.reporttime, endtime = m.endtime, 
    itismessage = m.itismessage, shortdesc = m.shortdesc, 
    longdesc = m.longdesc, severity = m.severity, 
    tc_source = m.tc_source, tc_state = m.tc_state, 
    ushr_state = m.ushr_state, mainroad = m.mainroad, 
    start_location = m.start_location, end_location = m.end_location     
FROM 
    chgman.tc_matched_raw AS m 
INNER JOIN 
    chgman.tc_data AS d ON d.tc_id = m.tc_id;

The result of the query is that EVERY row in table d is populated with data from the FIRST row of View m.

I am prepared for the embarrassment - please enlighten me as to what I have done wrong...

I think I may be able to answer my own question - it's the fact that the View derives its data from the destination table. I need to save the view as a temp table and THEN run the query. — CNIDog
– CNIDog, Commented Aug 17, 2018 at 15:54

Gordon Linoff · Accepted Answer · 2018-08-17 15:54:18Z

2

The from/update in Postgres works a bit differently from SQL Server/MS Access.

This should do what you want:

UPDATE chgman.tc_data d
   SET reporttime = m.reporttime, . . .
   FROM chgman.tc_matched_raw m 
   WHERE d.tc_id = m.tc_id;

You don't repeat the table in the FROM clause -- that is a new reference to the table.

answered Aug 17, 2018 at 15:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

CNIDog Over a year ago

That works, @Gordon. Replacing the JOIN with a WHERE clause did the trick. Thanks!

Collectives™ on Stack Overflow

PostgreSQL update not working as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related