Creating column based on other table's column in postgreSQL

Question

I have two tables which is 'table1' and 'table2' , table1 looks like this

and other table table2 looks like this

i want to make column "e_id" in table1 based on element's "id" in table2

so the result may look like this

the e_id should be dynamic ( like if element is found in the table2, it will assign its id in e_id column )

is there a optimized way to do this in postgreSQL?

i am currently using dbt tool, and i made table2 as a dict and compared with them with for loop for the column e_id — Jb-99
– Jb-99, Commented Apr 27, 2022 at 8:21
Does this answer your question? how to create column based on other column in sql? — pringi
– pringi, Commented Apr 27, 2022 at 8:36

eNca · Accepted Answer · 2022-04-27 10:23:24Z

1

Not sure about optimized way but you need to alter your table1 first then you do an update on it. cheers!

ALTER TABLE table1
ADD COLUMN e_id int;

UPDATE table1
SET table1.e_id = table2.id
FROM table2
WHERE table1.elements = table2.e

eNca

1,31315 silver badges28 bronze badges

answered Apr 27, 2022 at 8:41

luismcp

413 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1