|
View previous topic :: View next topic
|
| Author |
Message |
Rajkumar_n
New User
Joined: 07 Dec 2006
Posts: 51
Location: Bloomington
|
|
|
|
Hi,
Can any one help me in providing a query. Table with example records are as shown below.
| Code: |
Appl_no Load_TM Partic_Cd Cust Name
20001234 01-11-2009 P Jim Bob
20004322 03-10-2009 P Sara Beth
20004322 12-22-2008 S Billy Joe
20006587 03-04-2009 P Tom Wayne
|
Here I have a situation where the record can present more than one in table, in this case I have a requirement to take the records latest 'LOAD_TM' and update LOAD_TM with the latest date.
after doing the above the table should like the following
| Code: |
Appl_no Load_TM Partic_Cd Cust Name
20001234 01-11-2009 P Jim Bob
20004322 03-10-2009 P Sara Beth
20004322 03-10-2009 S Billy Joe
20006587 03-04-2009 P Tom Wayne
|
There can be two queries one for select and other for update. Can any one suggest me the select query to get the latest LOAD_TM for the record.
Note : Record Key is APPL_NO
Thanks and Regards,
Rajkumar Nerella |
|
| Back to top |
|
 |
Rajkumar_n
New User
Joined: 07 Dec 2006
Posts: 51
Location: Bloomington
|
|
|
|
Repositing the same as the table structure was not shown properly in first case
Hi,
Can any one help me in providing a query. Table with example records are as shown below.
| Code: |
Appl_no Load_TM Partic_Cd Cust Name
20001234 01-11-2009 P Jim Bob
20004322 03-10-2009 P Sara Beth
20004322 12-22-2008 S Billy Joe
20006587 03-04-2009 P Tom Wayne |
Here I have a situation where the record can present more than one in table, in this case I have a requirement to take the records latest 'LOAD_TM' and update LOAD_TM with the latest date.
after doing the above the table should like the following
| Code: |
Appl_no Load_TM Partic_Cd Cust Name
20001234 01-11-2009 P Jim Bob
20004322 03-10-2009 P Sara Beth
20004322 03-10-2009 S Billy Joe
20006587 03-04-2009 P Tom Wayne
|
There can be two queries one for select and other for update. Can any one suggest me the select query to get the latest LOAD_TM for the record.
Note : Record Key is APPL_NO
I HAVE APPL_NO and LOAD_TM IN MY SELECTION CRITERIA.
Thanks and Regards,
Rajkumar Nerella |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
Little Confused 
As per your example latest 'LOAD_TM' value is 03-10-2009.
And after update...only the the earliest 'LOAD_TM' value i.e. 12-22-2008 was changed to 03-10-2009...rest left untouched.
So, is this what you are looking for .
WTF |
|
| Back to top |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006
Posts: 51
Location: Bloomington
|
|
|
|
YES. there is one record with LOAD-TM AS 03-10-2009 and other record has 12-22-2008 .
Between these two records latest LOAD_TM IS 03-10-2009
We need to update the prior record to the same value 03-10-2009 .
Thanks and Regards,
Rajkumar Nerella |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
records and fields relate to files.
db2 tables are constructed of columns and rows.
try to use the correct terminology, please. |
|
| Back to top |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006
Posts: 51
Location: Bloomington
|
|
|
|
HI,
Sorry for the if the post is causing any confusion. I tried to provide space between each column but some how it didnt worked.
The above specified are column names and related data. Specified values are for db2 column names.
Thanks and Regards,
Rajkumar Nerella |
|
| Back to top |
|
|
maggiebms
New User
Joined: 23 May 2005
Posts: 16
|
|
|
|
Hi,
I think the below query can help you.
UPDATE <TABLE> T1 SET T1.Load_TM = (SELECT MAX(Load_TM) FROM
<TABLE> T2 WHERE T2.APPL_NO = T1.APPL_NO AND T2.LOAD_TM > T1.Load_TM))
Regards,
Maggie J |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
to all of You
please learn to use the code tags
it' s quite boring to have to edit your posts to make them readable
by adding the code tags |
|
| Back to top |
|
|
|
|
