Proc sql updating multiple columns
Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant.
He has authored 11 SQL Server database books, 23 Pluralsight courses and has written over 4700 articles on the database technology on his blog at a https://blog.
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.
This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31.
In recent times I have seen a developer writing a cursor to update a table. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31.
September 26, 2008 - pm UTC you can use mergemerge into t1using t2on (t1.x = t2.a)when matched then update set y = t2.b;if there exist duplicated by t2.a (more than one row with t2.a having the same value), the merge will fail at runtime when it encounters you trying to modify Y in a non-deterministic fashion. Can we use the WHERE clause in the merge statement?
I cannot tell you how to make something more efficient if I do not know what is impeding the efficiency in the first place.
When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc.
You can additionally use MERGE statement to do the same as well, however I personally prefer this method.
Let us clean up the clause by dropping the tables which we have created.
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.
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? ITEM_NO * ERROR at line 4: ORA-00904: "CLASS_TEST"."ITEM_NO": invalid identifier Any help will be appreciated. If i am updating & using non key preserved tables then exists seems to be quicker one! update account_allocation2 set (sup_hyg_acc_policy,sup_hyg_acc_add)= ( select sup_hyg_acc_policy,sup_hyg_acc_add from acc_hyg_driver where acc_hyg_driver.account_number = account_allocation2.account_number and exists ( select '1' from acc_hyg_driver where acc_hyg_driver.account_number = account_allocation2.account_number )) any ideas?? merge into account_allocation2 using acc_hyg_driver on (acc_hyg_driver.account_number = account_allocation2.account_number) when matched then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy, sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_addmerge into account_allocation2 using (select * from acc_hyg_driver where account_number in (select account_number from account_allocation2) acc_hyg_driver on (acc_hyg_driver.account_number = account_allocation2.account_number) when matched then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy, sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_add when not matched then insert (account_number) values ( null )the "using" query only needs to use the IN if acc_hyg_driver might have account numbers that are NOT IN account_allocation2, if acc_hyp_driver only has account numbers in that set, you can just use that table. But the volumes dealt with are account_allocation has 16 million acc_hyg_driver has 6 million and the update does around 6 million records, The current merge takes more than 5 hours with other bottle necks ( includes CPU sharing) which cant be helped!