Updating multiple tables in pl sql
I don't think i can use the merge statement here since there are more than 1 condition that has to be met for the target table to be updated. The way i understand it external tables don't have primary keys. 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.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.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. 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 & thanks pjp 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.
qty I want to update ORDERED_ITEMS table for a few records but exclude any book where BOOKS.bk_flag='X' Would you do this UPDATE ordered_item SET qty = new_qty WHERE order_date = xxxx and cust_id = xxxx and bkno not in (select bkno from books where bk_flag May 06, 2008 - am UTC tell me how you would do an update on a join here? D May 06, 2008 - am UTC and the types table plays into this HOW? You want to join basically three tables together, I can envision that jobs.item_no = class_test.item_no, but see NO WAY to add in TYPES to that join this question begs more questions than answers itself. Is it better to go for Create Table as Select (update info) ???? ) But i cant use the create for limitations on downtime of the table. Unfortunately i cant get the tkprof as i dont have access to. SQL courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.Compared to traditional travel-based training, SQL saves time and valuable corporate resources, allowing companies to do more with less. The Ask TOM team is taking a break over the holiday season. 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. Thanks for being a member of the Ask TOM community. This year we've answered over 2800 of your questions and followed up on 3500 reviews. 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.