Solved

Fast Update Query  required

Posted on 2008-10-30
10
620 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Verification of DataPump Export and Import 17 55
SQL Server Insert where not exists 24 41
Oracle DB monitor SW 21 48
run sql script from putty 4 35
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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