How to update all the value from one column to another-CodePudding

Table 1 -

col1	col2
1	443
2	449
3	479

Table 2 -

col1	col2
1	NULL
2	NULL
3	NULL

Note - col1 is not a PK

expected Output -

Table 2 -

col1	col2
1	443
2	449
3	479

This is my python code:

abc_df = pd.read_sql_query("UPDATE Table1 SET Table1_id = t2.col1 FROM Table1 t1
                INNER JOIN Table2 t2 ON t1.col2 = t2.col2", con=connection)

But My Actual OUTPUT is -

Table 2 -

col1	col2
1	443
2	443
3	443

CodePudding user response：

You need to update your UPDATE query -

UPDATE Table2 T2
   SET col2 = T1.col2
  FROM Table1 t1
 WHERE T1.col1 = T2.col1

CodePudding user response：

in your update query there is 2 part are wrong first one where set the table2 data, you used Table1_id = t2.col1 but you should used t2.col2 = t1.col2 and other wrong thing in join condition you used t1.col2 = t2.col2 but you should used t1.col1 = t2.col1

update your query as per following

Update Table2 T2 SET T2.col2 = T1.col2 FROM 
Table1 t1 WHERE T1.col1 = T2.col1

CodePudding user response：

The only thing I will add to existing answers is in general updates can be expensive operations, so put in a check to be sure you NEED to update it before you do it.

update table1 t1
set col2 = t2.col2
from table2 t2
where
  t1.col1 = t2.col1 and
  t1.col2 is distinct from t2.col2   -- added this check

Because of MVCC, it will actually create a new record and create a dead space where the previous record was (until the next vacuum), so it makes senses on many levels to not update a record if all you are doing is replacing the value.

I realize in your example you were replacing all nulls, but in general this is just a good practice.