PostgreSQL UPDATE FROM

Question

Apologies if this has been answered elsewhere, I'm afraid I need a little more clarification/brushing up on the UPDATE FROM clause in PostgreSQL.

Basically I have a temporary table with some intermediary computed stuff that I want to use to update the main table. This temporary table includes two foreign keys and a score, such as:

score   fk_offer    fk_searchprofile
65      1764        12345
...

I tested the rows to be updated with a select (the table temp_offerids_with_score contains the offers that need to be updated):

SELECT s.pkid, tmp.fk_offer, s.fk_category, tmp.score, tmp.fk_searchprofile
FROM 
            temp_weighted_scores_offers AS tmp
            INNER JOIN sc_sp_o_c_score AS s
            ON tmp.fk_offer = s.fk_offer
WHERE 
            tmp.fk_offer IN (SELECT fk_offer FROM temp_offerids_with_score)
            AND 
            s.fk_category = 1
            AND s.fk_searchprofile = 12345;

This correctly returns the expected number of rows (in this case 10):

pkid    fk_offer    fk_category  score  fk_searchprofile
1       47          1            78     12345
2       137         1            64     12345
3       247         1            50     12345
...

However, if I use the same in an UPDATE FROM:

UPDATE sc_sp_o_c_score
      SET score = tmp.score
FROM 
            temp_weighted_scores_offers AS tmp
            INNER JOIN sc_sp_o_c_score AS s
            ON tmp.fk_offer = s.fk_offer
WHERE 
            tmp.fk_offer IN (SELECT fk_offer FROM temp_offerids_with_score)
            AND 
            s.fk_category = 1
            AND s.fk_searchprofile = 12345;

the whole table, over 32000 rows, gets updated with the same (wrong, of course) score overall.

pkid    fk_offer    fk_searchprofile    fk_category score
1       47          12345               1           104 
2       137         12345               1           104
3       247         12345               1           104

What am I missing?

Thanks, Julian

EDIT: just in case this could be of any help - for the record, I'm migrating things from SQL Server here, where this is in fact a valid construct.

Answer 1

You are using the table to be updated also as a self-join (through reference in the FROM clause). Take that out and you should be good:

UPDATE sc_sp_o_c_score
  SET score = tmp.score
  FROM temp_weighted_scores_offers AS tmp
  WHERE tmp.fk_offer = fk_offer
    AND tmp.fk_offer IN (SELECT fk_offer FROM temp_offerids_with_score)
    AND fk_category = 1
    AND fk_searchprofile = 12345;