Updating multiple tables in oracle
( Client number is consist of branch, Number, Sub Number Currency Code ) We have one big transaction table around 1 million records, having many columns, and at many places client numbers are stored from account master under various columns 1. You told me to use 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; But I think my questions still reamin unanswered 1. In your answer only one number say client number will get updated. I have to update a transaction table where mapping is existing with old account no and new account number. AND blah blah blah.....) Since I don't know the relations between the table, I can't say what is the where clause to identify unique relation between CARS and PRICES. The other thing which is bothering me is the where clause of the update statement.I think the right one is like this: UPDATE carts c SET c.format_code = (select p.format_code from PRICES p where C.Secondly you have shown me to update table with "KEY PRESERVED" method. IT NEEDS THIS LOGICALLY regardless of the method used, so just add it (for if a row in OLD joins to more than one row in NEW -- you are hosed, you cannot update this data, therefore NEW has a unique/primary key, add it to facilitate the update) I had a similiar query and i tried applying to my tables.
1) Is the syntax below incorrect, and 2) if it is incorrect, then how can you update multiple rows using data from other columns, the way it's described below?
What you propose only works if I want to update all rows in the table being updated to the same value in all the other tables.
Also, creating views of this data isn't really applicable.
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 & thanks pjp 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. 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.
This question is Hi Tom, Due to migration to new system we have to change all our account numbers. June 28, 2005 - pm UTC yes, the predicate in the join for the fictional question without a sample table and data was wrong (everytime I wing it, try to answer WITHOUT TESTING, it comes out wrong. test cases are relevant :) Hi Tom, Thanks for answering my question.