Question 114769 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to update all the value from one column to another

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
Tags:

3

Answers


  1. 0

    You need to update your UPDATE query –

    UPDATE Table2 T2
   SET col2 = T1.col2
  FROM Table1 t1
 WHERE T1.col1 = T2.col1
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search