Update table - relation does not exist

Question

I am trying this query, however is not working: The idea is copy data from one table to another.

UPDATE  A
SET     A.name_en = B.name
FROM    country_name as A
        INNER JOIN country as B
            ON A.id = B.id

I am getting this error:

Error in query: ERROR: relation "a" does not exist
LINE 1: UPDATE A

Why?

Answer 1

You don't need the extra join here at all. You're doing an inner join so you can just do it with from and where clause instead:

UPDATE  country_name
SET     name_en = B.name
FROM    country AS B
WHERE   counry_name.id = B.id;

The only time you need to do the UPDATE sometable ... FROM sometable a ... WHERE sometable.id = a.id trick is when you need to do an outer join against the table being updated.

It's unfortunate that PostgreSQL's UPDATE ... FROM ... feature doesn't let you use explicit join syntax.

Answer 2

I think you need to write the real name of the table because the action will be taken outside of the query itself. Therefor, the association will be over.

UPDATE  country_name
SET     A.name_en = B.name
FROM    country_name as A
        INNER JOIN country as B
            ON A.id = B.id
WHERE   A.id = B.id;

took the where from the other person's answer just to make sure you don't make a mistake in your DB.