• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Access Client Solutions, ACS, FHG, Four Hundred Guru, IBM i, Run SQL Scripts, SQL, System i Navigator

    Sponsored by
    New Generation Software

    Free NGS-IQ Webinar: A Better Way to Connect IBM i Data and Microsoft 365 Users.

    Business users rely on Excel, SharePoint, Access, and other Microsoft tools to view, analyze, and share data. But what if your data is too sensitive and complex to simply open up to non-technical users via ODBC and Microsoft 365’s query functions?

    Attend our webinar on June 22, 2022, and see how you can make the people you support smile even as you centrally manage and secure IBM i data access.

    www.ngsi.com– 800-824-1220

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Automation For The Masses – Here Come The Bots Monoliths, Microservices, And IBM i Modernization: Part 1

    6 thoughts on “Guru: Edit Result Sets in Run SQL Scripts”

    • John Rusling says:
      October 7, 2019 at 9:39 am

      Hi Paul, there must be a certain version of ACS Run sql scripts this applies to as when i double-click i don’t get the edit option?

      Reply
      • Peter says:
        May 20, 2020 at 11:30 am

        Hi John, I have same issue. Is it version related ?
        I am at :
        Version: 1.1.6.1
        Build id: 6431
        October 19, 2016 9:29:49 AM EDT

        Reply
    • David Larsen says:
      October 7, 2019 at 12:46 pm

      Paul, Thank you for that tip. Even though it seems so simple, it is immeasurably valuable and another tool that saves time.

      Reply
    • Bill Pahl says:
      October 8, 2019 at 5:18 pm

      This is great! I get tired of typing the whole thing again with an UPDATE table SET… construct.

      Reply
    • Nigel Tufnel says:
      March 27, 2020 at 9:17 am

      Awesome tidbit! Does anyone know of a way to change “run SQL scripts” to dark mode?

      Reply
    • Laura says:
      July 6, 2020 at 3:41 pm

      I was able to do the update but now I can’t. What is wrong of my setup?

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 57

This Issue Sponsored By

  • ARCAD Software
  • Profound Logic Software
  • WorksRight Software
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • Sometimes Even DIYers Need A Little Help
  • Monoliths, Microservices, And IBM i Modernization: Part 1
  • Guru: Edit Result Sets in Run SQL Scripts
  • Automation For The Masses – Here Come The Bots
  • Power7 And Power7+ Will Truly Be Dead At The End Of 2020

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • POWERUp Brings IBM i Base Back Together in the Big Easy
  • New Nav for i Brings New Stuff to You
  • Why Infor’s IDF Is Important for Customer Innovation
  • Four Hundred Monitor, May 25
  • IBM i PTF Guide, Volume 24, Number 21
  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.