Search code examples
sqlpostgresql

How to insert data from another table while column values equals in Postgresql?

In Postgres, suppose I have two tables.

Table A:

ID Gender
1
1
2
2
3
3

Table B:

ID Gender    
1   F
2   M
3   F

How could I insert the gender values from Table B to Table A and get new table A like this: Table A:

ID Gender
1  F
1  F
2  M
2  M
3  F
3  F

I tried commands like:

insert into a(gender) select gender from b 
where a.id=b.id;

and it gives me error info:

there is an entry for table "a", but it cannot be referenced from this part of the query.

Solution

  • Please try this:

    UPDATE TABLE "A" Set "Gender" = "B"."Gender"
FROM "B"
WHERE "A"."ID" = "B"."ID";

    Hopefully it will help you.