Guru: Edit Result Sets in Run SQL Scripts

October 7, 2019 Paul Tuohy

Before getting into the detail in this article, I want it to be clear that I do NOT (in any way) advocate the direct editing of data in a production database. But when it comes to a test database, then the ability to directly edit data is invaluable.

Back in the days of System i Navigator, you could right click on a table, select the Edit option and a window would open containing the contents of the table. You could directly edit the contents of any cell. Rows could be inserted or deleted using the Rows option on the menu.

The same Edit context menu option is not available when you right click on a table in Access Client Solutions (ACS).

But there is an alternative, for editing data, in Run SQL Scripts. This alternative offers one major advantage, over the System i Developer option, and one minor disadvantage.

In order to be able to edit the data in a result set, you enter an SQL SELECT statement and at the end of the statement, you add FOR UPDATE. For example:

select * from employee for update;

The result set will, as usual, be displayed in the panel at the bottom of the Run SQL Scripts window or is a separate window, depending on your settings.

Now, all you have to do is double click on a cell in the result set and the contents of the cell become editable.

The major advantage you get with this approach is that the result set, for editing, is based on the select statement. This means you can edit a subset of any data in a table (columns and/or rows) as opposed to editing the full table.

select empno, firstnme, lastname
from employee
where workdept = 'D11'
for update;

The minor disadvantage is that, if you want to insert or delete a row, you must use an SQL INSERT or DELETE statement.

All in all, I think this edit option is a step forward.