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.

score 37 · Accepted Answer · answered Jun 27 '13 at 18:32

37

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.

answered Jun 27 '13 at 18:32

ParveenArora

711
1
5
13

score 14 · Answer 2 · edited Jan 23 '14 at 06:26

14

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;

edited Jan 23 '14 at 06:26

lulalala

17,572
15
110
169

answered Jun 27 '13 at 18:41

tx-911

427
1
4
17

Java Devil · Answer 3 · 2013-06-27T03:46:38.463

4

What you want is

UPDATE Table2
SET ref_id2 = table1.ref_id1
FROM table1
WHERE table1.id = table2.id
AND table1.a_ref1 = table2.b_ref1;

Edit This is what you actually want

As seen here (crudely)

edited Jun 27 '13 at 03:46

answered Jun 27 '13 at 03:34

Java Devil

10,629
7
33
48

score 0 · Answer 4 · answered Jun 27 '13 at 03:28

0

I think this should work:

UPDATE Table2
SET ref_id2 = ref_id1
FROM Table2 
   JOIN Table1 ON 
       Table2.Id = Table1.Id AND Table2.a_ref1 = Table1.b_ref1

answered Jun 27 '13 at 03:28

sgeddes

62,311
6
61
83

user2480596, your answer runs, but the column is not getting updated. my data is different but purpose is entirely the same, i ran the following query UPDATE public.clean_trips_byobu SET trip_dist=(SELECT bktp_mt_total FROM clean_trips JOIN clean_trips_byobu ON clean_trips_byobu.obu_id = clean_trips.obu_id AND clean_trips_byobu.trip_id = clean_trips.bktp_mt_total); it returns: Query returned successfully: 284193 rows affected, 69580 ms execution time. but the column trip_dist is not changed at all. – NamertaArora Jun 27 '13 at 06:14

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

4 Answers4

Linked