1

I just got a new laptop with Windows 10 which can't use the "Data Transfer to iSeries" function in excel. I've tried to re-install the add-on again but still does not work.

I'd like to transfer file which has a list of objects, libraries, and saved date for restoration on AS/400 (using BRMS function) For example : 

   OBJ          LIB1        LIB2        DATE
   OBJ0001      FROMLIB01   TOLIB01     20/12/2018
   OBJ0002      FROMLIB02   TOLIB02     20/12/2018
   OBJ0003      FROMLIB03   TOLIB03     20/12/2018

So, I need to find the way to edit record in the physical file instead of transferring the file from the PC. I know that STRSQL can edit records but I'm not familiar with the SQL commands. Can anyone guide me on how to edit many records using SQL?

Dave M
  • 4,514
  • 22
  • 31
  • 30
KWAN
  • 11
  • 1
  • 3

1 Answers1

1

This isn't exactly about AS/400 but about SQL in General. See here for a general overview of the syntax and here for a quick start guide.

For changing records (on the AS/400), the standard UPDATE clause applies. Probably you're not aware that the F4 prompting function also works in interactive SQL (strsql). Try it out, it's definitely helpful for a beginner.

A general starting statement would be UPDATE LIBRARY/TABLENAME SET field1='value1', … WHERE field1='value0'…

The SET Clause lists comma-separated field-value pairs to write (update) and the WHERE clause restricts these updates to the desired records. The WHERE-Clause can be made arbitrarily complex and list many restrictions with AND/OR boolean logic for precise selection of which records to update. See tutorial for WHERE-Clause examples.

PoC
  • 236
  • 1
  • 8
  • Thank you very much for your sharing. I've tried to insert record to PF by using STRSQL and it's work, then i tried to use SQL command in my CL program because i need to insert too many record, after compiled and call PGM, The job log tell about "Member OBJ0001 not journaled to journal *N". I use DSPFD to see file attribute and i found that " File is currently journaled . . . . . . . . : No". Do i have to change my PF attribute which attached with journal? How to change this value? – KWAN Feb 06 '19 at 10:42
  • I'm not fluent with Journalling on this platform. It would be probably more helpful if you check WRKJRN and WRKJRNA. Maybe see also RUNSQLSTM for bulk-INSERTs. – PoC Feb 07 '19 at 11:35