How to update oracle table in Python using data from another table

Question

I am using the below code

SQL = 'UPDATE TBL1 T1 SET T1.COL1 = (SELECT T2.COL1 FROM TBL2 T2 WHERE T1.ID = T2.ID)'
tmp_cursor=self.DB_conn.cursor()
tmp_cursor.execute(SQL)

I am not getting any error but the table is not getting updated.

Any suggestion on what is wrong?

Have you tried the to update using sqlplus, toad, sqldeveloper or any other oracle client? — Gryu
– Gryu, Commented Jan 2, 2020 at 21:40
Verify whether or not your program has committed the transaction. — dsh
– dsh, Commented Jan 2, 2020 at 21:43

blhsing · Accepted Answer · 2020-01-02 21:45:21Z

2

As pointed out in the documentation, you would need to commit the changes as a transaction for them to be available to other users and sessions:

When you manipulate data in an Oracle Database (insert, update, or delete data), the changed or new data is only available within your database session until it is committed to the database.

So adding the following will do:

self.DB_conn.commit()

answered Jan 2, 2020 at 21:45

blhsing

109k9 gold badges89 silver badges132 bronze badges

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

1 Comment

Christopher Jones Over a year ago

The explicit commit() causes a round-trip to the DB, which impacts performance scalability. Review the doc on using autocommit mode, which has its place.

Collectives™ on Stack Overflow

How to update oracle table in Python using data from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related