I am trying to update table A with data from table B. I thought I could do something like:
UPDATE A
SET A.name = B.name
WHERE A.id = B.id
but alas, this does not work.
Anyone have an idea of how I can do this?
4 Answers
Your query does not work because you have no FROM clause that specifies the tables you are aliasing via A/B.
Please try using the following:
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A, TableNameB B
WHERE A.ID = B.ID
Personally I prefer to use more explicit join syntax for clarity i.e.
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A
INNER JOIN TableName B ON
A.ID = B.ID
The answers didn't work for me with postgresql 9.1+
This is what I had to do (you can check more in the manual here)
UPDATE schema.TableA as A
SET "columnA" = "B"."columnB"
FROM schema.TableB as B
WHERE A.id = B.id;
You can omit the schema, if you are using the default schema for both tables.
Related