CODEWITHSUNDEEP
sqlpostgresqlsql-updatesubquery
Amin Negm-Awad
Amin Negm-Awad

Reputation: 16660

Update a table from another table using subselect

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions