Reputation: 16660
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.
Upvotes: 1
Views: 108
Reputation: 1271241
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.
Upvotes: 1