• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Use SQL to Easily Update Multi-Key Files

    July 27, 2005 Hey, Ted

    Updating a single record in a file that has a lot of key fields often requires SQL commands with complicated WHERE clauses. I use a technique with web applications that greatly simplifies the WHERE clause. I have never seen this technique published before, so I hope you can use it.

    When I retrieve a record that will be updated through a Web browser, I select the relative record number along with the data.

    SELECT RRN(MyFile) as storedRRN, MyFile.* 
      FROM MyFile 
     WHERE ENBBTYPE = 'COPY' and ENBBTCH# = 4062
    

    When I update, I need only refer to the relative record number.

    UPDATE MyFile 
       SET SomeCode = 'FRO', Description = 'Front' 
     WHERE RRN(MyFile) = storedRRN
    

    –Chuck Wakelee, Cardone Industries

    Since I favor making things as simple as possible, I am glad to publish Chuck’s tip. It may not be worth my mentioning it, but Chuck’s SQL statements are bare-bones illustrations. To use this technique in a program will require the use of host variables.

    –Ted


    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    New Generation Software

    On Demand Video

    Data Visualization and Analytics
    with NGS-IQ and Microsoft Power BI

    Power BI is a popular visualization and analytics solution, but it’s only as good as the data your analysts can draw from your IBM i database.

    NGS-IQ simplifies and automates the cleansing, transformation, and delivery of IBM i data to Power BI reports and insights. Watch our new video to learn and see how NGS-IQ supports Power BI users while enabling you to maintain control and security.

    www.ngsi.com – 800-824-1220

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Cozzi Updates RPG xTools, Partners with Linoma DRV Technologies Gets Hip to Forms

    Leave a Reply Cancel reply

Volume 5, Number 29 -- July 27, 2005
THIS ISSUE
SPONSORED BY:

ProData Computer Svcs
Advanced Systems Concepts
Patrick Townsend & Associates

Table of Contents

  • Of Middle-Tested Loops
  • Use SQL to Easily Update Multi-Key Files
  • Admin Alert: To Each Its Own in Spooled File Management

Content archive

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

Recent Posts

  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22
  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM

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 © 2023 IT Jungle