1

Using postgresql I have a table A with columns company_name, department_name, some other data, and department.

I have another table B with simply company_name, department_name, and did. (This are two tables with a relationship in between, but I made a view from it to make syntax easier.)

I want to set A.department to B.did of the row with matching names. It is a part of a normalization process.

According to this Q&A I tried:

UPDATE A 
SET department=did 
FROM A AS A 
INNER JOIN B 
  ON A.company_name = B.company_name AND A.department_name=B.department_name;

But I get the result, that A.department for all rows are set to the same value.

(I have also tried different syntax from here, but get syntax errors as expected.)

Why isn't that working and more specifically, why aren't the rows matched properly. When I try a SELECT on that join, I get the expected result.

Community
  • 1
  • 1
Amin Negm-Awad
  • 16,582
  • 3
  • 35
  • 50

1 Answers1

1

In Postgres, you want to write the query like this:

UPDATE A 
    SET department = B.did 
FROM B 
WHERE A.company_name = B.company_name AND
      A.department_name = B.department_name;

When you mention the A table in the FROM clause, it is a separate reference to A, nothing to do with the table being updated. The simple solution is to refer to the table only once.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    @AminNegm-Awad: this is also [documented in the manual](https://www.postgresql.org/docs/current/static/sql-update.html): "*Note that the target table must not appear in the from_list, unless you intend a self-join*" –  Jan 24 '17 at 21:55
  • Thanks for the additional information. I tried it a similar way found in the referred Q&A, which seemed to be intuitive to me. Therefore I guessed to have a pure syntactical problem and did not see the hint in the docs. – Amin Negm-Awad Jan 24 '17 at 22:20