Solved

Fast Update Query  required

Posted on 2008-10-30
10
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Assisted Solution

by:chaitu chaitu
chaitu chaitu earned 200 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 300 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

695 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