Updating data from one table to another
Using cursor is ok, but it brings with a un-toleratable speed when operating on a large table. REGISTRATION where vistemp.registration.bin=vis.registration.bin)/am i right? Whenever anyuser insert/update anything,system date is inserted with them and i am exporting that data by using that system date."AM I IN RIGHT WAY? In my B(big table record-79186) and in S(small table record-12871). Can you please tell me which option performs better if the number of records are in millions. I have a table t1 which is having all orders information.Even the idea create a temporary table holding only primary key and Column b, and then apply cursor to it is slow. "c)if i have a composite key then"---where a.key1=b.key1 and a.key2=b.key2 and----)am i right for both insert/update by given ur advice? Another table summary of orders tt1 which is having current year summation and respective previous year summation columns. Type ------------------------------- -------- ------------ ORDER_NUMBER NUMBER(10) ORDER_DATE DATE CY_ORD_AMT NUMBER PY_ORD_AMT NUMBER Order date is current year date.Next, build the query like the one below: This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
You can select multiple tables by holding down the Update Query from the popup menu.
It is best to use the above update of a JOIN if possible (optimizer has best chance of getting a good plan). then, we'll all know.(but #2 looks "wrong" if a's primary key is deptno,dname -- that means a given deptno can return many dnames and a dname many deptnos and hence the "deptno = ( select .... JOIN_COL); Now, if I do a regular simple update (like above) it takes for ever (never actualy had a chance to compleat it).
It will need the primary key constraint on B though, if you do not have one BUT is unique, you can:[email protected] I can think of another instance (at least with v8.1.5) where the second example (updating the table via a subquery) works better than the first example (updating the joined tables). My last PL/SQL code was just a test as i was interested more in the performance during update, so i missed the commit point. )" would return more than one row in general leading to an error).... I have a table with about 5,000,000 records, the table have about 70 columns. I tried all kinds of variations with loading the data into collections and using index-by-varchar tables with no suceess in improving preformance by much.
Continue Reading Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.
I wish to use an UPDATE query to change data in one column of a table to the values from another table.