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
Solved

Fast Update Query  required

Posted on 2008-10-30
10
621 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

861 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