Proc sql updating multiple columns who is dating simon baker
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. 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. Along with 16 years of hands on experience he holds a Masters of Science degree and a number of database certifications.For any SQL Server Performance Tuning Issue send an email at [email protected] Sales Supplier, Purchase supplier, Wholesale Supplier ..many other columns how can i update in single SQL ( I have to update around 70 columns and some columns may have null values such as Purchase supplier ..)2. You need to have the table you are updating from have a unique or primary key constraint. 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.
ID = @parameter Hope I succeded in making it clearer.
If you are at the conference, drop into the Groundbreaker area and say Hello. etc I have a mapping table where "ALL" old account number and new account numbers are present.
Check out all our database development sessions at OOW19 Thanks for the question, Parag Jayant Patankar. I have to update a transaction table where mapping is existing with old account no and new account number.
However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.
As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table.