2

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...

The Impaler
  • 45,731
  • 9
  • 39
  • 76
CNIDog
  • 374
  • 3
  • 18
  • 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 Aug 17 '18 at 15:54

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786