Solved

Fast Update Query  required

Posted on 2008-10-30
10
618 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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 20

Assisted Solution

by:chaitu chaitu
chaitu chaitu earned 200 total points
Comment Utility

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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
Mark Geerlings earned 300 total points
Comment Utility
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
Comment Utility
Thanks for solution.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now