I put a primary key on B(id) because if there is NO SUCH constraint -- the update is totally ambigous -- if there is more then 1 value of B1 in B for a given ID -- we cannot tell what row in B should be used to update the row in A. dear sir , Acually my problem is huge that that simple , i just try to give u a sample of my problem.i just need to know the best way to update some column value that depends on other columns value in same table (or /and ) in other tables .specially when one of them is a date.i do made a script that run every day at 5 am. March 21, 2004 - pm UTC they are derived columns and should not be stored -- especially something like "20 years old flag" as that can change at any is, and will remain, my answer unless you give me a real world scenario that would demand otherwise (the 20 year old flag should NOT be stored, period) Hi I have three tables, A, B and CA is parent of B and C, A has a composite PK deptno and dname I would like to know if following delete SQLs are equivalent1.------------------------------------------delete bwhere(deptno, dept_name) in (select a.deptno, dname from a, c where a.deptno = c.deptno and a.dname = c.dept_name);2.-----------------------------------------delete bwheredeptno = (select a.deptno from a, c where a.deptno = c.deptno and dname = dpt_name)and dept_name = (select a.dname from a, c where a.deptno = c.deptno and a.dname = c.dpt_name); Are they :-?
If you are at the conference, drop into the Groundbreaker area and say Hello. Every monththe client office is to give data(NEW & EDITED) "BY DATE RANGWISE" to the headoffice in CD. Thank u very much for ur kind & very helpful reply. Now we can update the join: update ( select a.pop, from taba a, gtt b where = ) set pop = cnt/and thats it. Hi Tom, Im selecting approximately 1 million records from some tables and populating another set of tables.
Check out all our database development sessions at OOW19 This may seem like a simple question: Update Column a1 in Table A with all data in Column b1 in Table B. The Headoffice is merge the data into their system. For migration data first of all i create another temporary user named VISTEMP then cotinuing this kinds of codeinsert into VISTEMP. Its working very nicely.i will greateful to u forever.i was suffering by this problem for a long i am using the following /insert into VISTEMP. i could envision problems with only a second of granularity on a clock -- you could miss a record or two due to that.c) er? Here the source tables have data with leading spaces and the target data should be without spaces.
When the Show Table window appears, select the tables that you wish to use in the query and click on the Add button.
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.
You could create a trigger on table B that updates table A every time field_2 on table B is updated.
I have a SQL query where I am trying to update a column in a table (tbl A) from data in another table (tbl B).
When the subquery fails to find a matching row in tbl B, the subquery returns NULL.
But since the UPDATE has no WHERE clause, it will update all rows, even those where the subquery returns NULL.
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.