?
Solved

Fast Update Query  required

Posted on 2008-10-30
10
Medium Priority
?
631 Views
Last Modified: 2013-12-07
We have two tables
1)
Plm ( PlmNo is the primary key)
which contains the following rows:
PlmNo AdDate Type StDate
101                  111    somedate
102                  131    somedate

2)
PlmLine (PlmNo is the foreign key, PlmNo & LineNo is the candidate key)
which contains the following rows:
PlmNo LineNo    SerDate
101       1         somedate|null
101       2         somedate|null
102       1         somedate|null
102       2         somedate|null

If the Type is 131 AdDate = min(SerDate) for that particular PlmNo
else
AdDate = StDate

if the min(SerDate) is null then AdDate should be StDate for that particular PlmNo.

I need a update query that i can write in my PL/SQL Function.

I need a fastest update query.
0
Comment
Question by:srikanthradix
  • 4
  • 3
  • 3
10 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22846949
This statement will update the records for "type=131".  Note that if there are no records in plmline, addate will remain null.

update plm p1
set p1.addate = (select min(p2.ser_date from plmline p2
where p2.plmno = p1.plmno)
where p1.type = 131

This statement will update the records where is *NOT* =131.

update plm p1
set p1.addate = stdate
where p1.type <> 131

Or, if you really prefer one, more complex statement, this statement should work:

update plm p1
set p1.addate = decode(p1.type,'131',(select min(p2.ser_date from plmline p2
where p2.plmno = p1.plmno), stdate;

0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22847028
markgeer,

little bit syntax errors in the above query.

update plm p1
set p1.addate = decode(p1.type,'131',(select min(p2.ser_date) from plmline p2
where p2.plmno = p1.plmno), stdate);
0
 

Author Comment

by:srikanthradix
ID: 22847199
Shouldn't i write group by clause since we are using aggregate function here?

decode(p1.type,'131',(select min(p2.ser_date) from plmline p2
where p2.plmno = p1.plmno group by p2.plmno), stdate);

And also, what if p2.ser_date is null?  i need to update adddate with stdate. How about that?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 20

Assisted Solution

by:chaitu chaitu
chaitu chaitu earned 800 total points
ID: 22847270

update plm p1
set p1.addate = decode(p1.type,'131',NVL((select min(p2.ser_date) from plmline p2
where p2.plmno = p1.plmno group by p2.plmno),stdate), stdate);
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22847282
IF ur using like this then u need to use group by clause but in ur case no need to select p2.plmno in the sub query so group by clause is not required.
select p2.plmno,min(p2.ser_date) from plmline p2
where p2.plmno = p1.plmno group by p2.plmno
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22849565
Yes, chatiuu is correct.  I missed a ")" in the syntax examples I gave you after "min(p2.ser_date".

And yes, there is no need for a "group by" clause since the sub-query does not select any ungrouped columns.  When the only column selected by a query has a group operator (like: min, max, avg, or count) you don't need a "group by" clause.
0
 

Author Comment

by:srikanthradix
ID: 22851561
Actually, the plmline table has a temp table(plmline_temp)  into which data will be inserted before it is inserted into the actual table(plmline). and then temp table will be truncated.

So, temp table always has less data than plm(p1) table. The difference is immense in millions.

But when i execute the following query with plmlime_temp table, it is updating all the rows(which is in millions) where type is 131.

update plm p1
set p1.addate = decode(p1.type,'131',NVL((select min(p2.ser_date) from plmline_temp p2
where p2.plmno = p1.plmno group by p2.plmno),stdate), stdate);

Is there any way i can do it with the help of joins which i assume will make the query faster?
0
 

Author Comment

by:srikanthradix
ID: 22851675
I am trying something like this:

"But this is not working"

UPDATE plm t1 SET (adDate) =
 
NVL((SELECT MIN(t2.ser_Date) FROM plmline_temp t2
where t1.plmno = t2.plmno GROUP BY t2.plmno ), t1.stdate)
 
FROM plm t3 INNER JOIN plmline_temp t4
ON t3.plmno = t4.plmno
 
WHERE t1.type = '131';
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1200 total points
ID: 22851950
What is not working?  Do you get an Oracle error?  Or, do you get results different from what you expect?

You may need to add a sub-query to limit the rows updated in plm to only those rows that have corresponding records in plm_line_temp like this:

UPDATE plm t1 SET (adDate) =
NVL((SELECT MIN(t2.ser_Date) FROM plmline_temp t2
where t1.plmno = t2.plmno GROUP BY t2.plmno ), t1.stdate)
FROM plm t3 INNER JOIN plmline_temp t4
ON t3.plmno = t4.plmno
WHERE t1.type = '131'
and  t1.plmno in (select t5.plmno from plmline_temp t5);
0
 

Author Closing Comment

by:srikanthradix
ID: 31511901
Thanks for solution.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question