Update a single record where multiple records match selection criteria
Last Post 22 Feb 2013 09:45 AM by JJ Dahlheimer. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Rex Reese
New Member
New Member
Posts:44

--
22 Feb 2013 08:20 AM

I can't figure out how to accomplish this.

I have a file that I want to update using SQL.  I want to update ONLY the oldest record.  The record contains a date and time field.  However doing a select, returns multiple records matching my criteria.   How can I update only the oldest record in one UPDATE statement?

Here's the select statement that returns multiple records.

select * from myFile                  
where chrcno = :myReceiver and chisbn = :myIsbn and chsts = 'RCV'                              

order by chrcvdt,chrcvtm

 

novacancy
Basic Member
Basic Member
Posts:125
Avatar

--
22 Feb 2013 08:50 AM

maybe something similar to this

update myfile a                                           
set a.field1='x'                                          
where a.field2=:somevalue                                 
  and a.datetime=(select min(b.datetime) from myfile b    
                   where b.field2 = :somevalue )         

Regards

Enduring the V5R3 blues
JJ Dahlheimer
Advanced Member
Advanced Member
Posts:626

--
22 Feb 2013 09:45 AM
Above solution looks better than mine. But you could also look at using RRN, though this not always the most reliable.
You are not authorized to post a reply.

Acceptable Use Policy