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.
Please try this:
UPDATE TABLE "A" Set "Gender" = "B"."Gender"
FROM "B"
WHERE "A"."ID" = "B"."ID";
Hopefully it will help you.
Have you tried any joins ? (i.e. inner or outer)
SELECT a.ID, b.Gender
FROM tableA a INNER JOIN tableB b
ON b.ID = a.ID;
However, this would be considered as separated result set or you could do the insert operation
Postgres throws and error on @Mabu Kloesen's answer. Should be just update <table_name>, not update table <table_name>.
Try this:
UPDATE a set Gender = b.Gender
FROM b
WHERE a.ID = b.ID;
