I'm try to update a column in table B with a value in table A where the passport_no match.

Below is my sql query.

update tabel_b
  set b.country_id = a.national_id
from table_a a
  join tabel_b b on b.passport_no = a.passport_no
where a.is_deleted = false;

On executing, I get ERROR: column "b" of relation "tabel_b" does not exist

How can I go about this please.

CodePudding user response：

The immediate error is that you can't qualify the column you want to update. So it should be set country_id = ...

The bigger problem is however your join.

As documented in the manual

Do not repeat the target table as a from_item unless you intend a self-join

So the correct statement most probably should be:

update tabel_b
  set country_id = a.national_id
from table_a a
where b.passport_no = a.passport_no
  and a.is_deleted = false;