Add value from another table by comparing tables columns

All we need is an easy explanation of the problem, so here it is.

I have 2 tables in postgres like:

table A:

unique_coords unique_id
a 1
b 2
c 3
d 4
. .

table B like:

coords id
a
a
c
d
c
c
d
c

I want to fill the id column in table B based on table A. So the result will be like this:

coords id
a 1
a 1
c 3
d 4
c 3
c 3
d 4
c 3

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is a simple UPDATE join syntax

UPDATE tableB
SET id = unique_id
FROM tableA
WHERE tableA.unique_coords = tableB.coords;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6f820b75952c90ae7cdeadec419f45f6

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply