Re: Update information on SQL

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I'm suggesting that you start journaling against that physical file. 
Then you can query the journal for changes - I don't remember if it will 
inform you of the RRN, but it probably does.

Is this for a test scenario? I can't think of a business reason to be 
concerned about this, so I just wonder.

Another way, if for test only, could be to have an extra column with a 
flag for updated - put a "U", say, in that column in your UPDATE 
statement. Clear it once you are done checking.

Or have a "date changed" column or "timestamp changed" column. Do 
whatever you need in order to check for update, such as changedate = 
max(changedate) from a table expression.

HTH
Vern

On 1/16/2013 5:22 PM, A Paul wrote:
> Hi there,
>   
> I've an SQLSRC statement embedded in RUNSQLSTM. My spool file tells me how many rows have been updated.
> But, does anyone know how to find the updated rows information without actually going back to the database file to query the records updated.
>   
> UPDATE Afile A
> SET A.Netamt = (SELECT replace(B.net_amount , ',' , '')
> FROM Bfile B
> WHERE A.date1 = B.date1), A.source ='1'
> WHERE EXISTS
> (SELECT B.net_amount
> FROM Bfile B
> WHERE A.date1 = B.date1)
>   
> This updates me 120 rows, I would like to see which rows it updates Netamt & source to 1 after running SQL.
> I could SQL
> Select A.Netamt, A.source from Afile A, Bfile B
>    where A.date1 =B.date1
>   
> Thank you!
>   
> p.s: can anyone recommend best SQL/400 book, sorry if this question does nt relate to this forum.