How to copy one column of a table into another table's column in PostgreSQL comparing same ID

Question

I need to copy ref_id1 from table1 TO the column ref_id2 in the table2 the two things matching will be : id (same column name), a_ref1 & b_ref1 (column names are different but numerical value will be identical).

Table1

ID      ref_id1                     a_ref1 9     2.3456762498;               1367602349 9     1.61680784158;              1367653785 9     2.63461385408;              1367687746 9     0;                          1367688520 9     0.780442217152;             1367740313 9     3.18328461662;              1367773889 9     0.775471247616;             1367774978 

Table2

ID          b_ref1                      ref_id2 9        1367602349; 9        1367740313; 9        1367774978; 2        1357110511; 2        1357186899; 2        1357195928; 2        1357199525; 

In a nutshell need to copy ref_id1 to ref_id2 by comparing id and a_ref1 with b_ref1, Please let me know how to do that.

Answer 1

UPDATE public.clean_trips_byobu SET trip_dist = clean_trips.bktp_mt_total FROM public.clean_trips  WHERE public.clean_trips.obu_id = clean_trips_byobu.obu_id AND clean_trips.bktp_trip_id = clean_trips_byobu.trip_id; 

Hope it will work for you.

Answer 2

UPDATE Table2 --format schema.table_name SET  ref_id2 = table1.ref_id1 FROM table1 -- mention schema name WHERE table1.id = table2.id AND  table1.a_ref1 = table2.b_ref1;