Updating one table from another table
Asked: June 28, 2005 - am UTC Answered by: Tom Kyte - Last updated: June 18, 2013 - pm UTC Category: Database - Version: 9.2 Viewed 100K times! I want to write SQL ( not PL/SQL ) to update transaction table from mapping table at one stroke. regards & thankspjp update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.new_account_number ) set old_account_number = new_account_number;disabling foreign keys during the operation and enabling them afterward. This question is Hi Tom, Due to migration to new system we have to change all our account numbers. Don't you mean....update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number;(ie in the subquery, match the old account numbers not try matching the old in the old table to the new in the mapping table)Of course if the original posting had the CREATEs/INSERTS etc, it would have a quick and east test. The Balance History table has a Balance Date field that can be checked to determine which record for a given Acct No has the most recent Balance.The two tables (Accounts and Balance History) are joined by the Acct No field. Thanks in advance, Base Noob Hello @Base Noob It is not typical to duplicate information in a relational database.If it is not possible can you show me how to write "GOOD" sql ? i.e, one column of a table should get updated from another column of another table. regards & thankspjp June 29, 2005 - am UTC select as many old columns as you want and as many new columns as you want??? But when i did the same with the syntax given i got this error ora-01779 cannot modify a column which maps to a non key preserved value. I tried doing the same through this update ( select old. But since the UPDATE has no WHERE clause, it will update all rows, even those where the subquery returns NULL.
In many times we need to update column(s) of a table based on the data of another table column(s). Let's show example by creating table and entering values into it.
I have a SQL query where I am trying to update a column in a table (tbl A) from data in another table (tbl B).
This works fine when I try to update all the records in tbl A, however, in this case I only have missing data which I have identified and populated in tbl B.
SELDATE) B WHERE "Accounts"."Acct No" = B."Acct No") EDIT 9/28/2017: The previous UPDATE statement bothered me enough to post a much preferred solution which can be retrieved anytime and is not copied into the main 'Account' record.
Additionally, since it is a query, it allows the statement to be stored in the Query section.
When I try to update only the missing data the non-matched data is also updated but to NULL. The reason some of your rows are getting NULLs updated is due to the subquery.