• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Update Only One of Duplicate Records

    December 5, 2007 Hey, Ted

    We have a file that has no unique key. This means that we sometimes get duplicate records in the file. This does not cause us problems in RPG programs because we can read a record, make changes to it, and update it, whether we’re using native I/O or an SQL cursor. However, having duplicate rows is a problem when we use SQL outside of programs. If two or more records are identical, there is no way to code a WHERE clause that will select only one record. Or is there?

    –David

    It is ugly, but possible, David. Assume table MYDATA, containing the following information:

    Name          Age  Location
    ============  ===   ========
    Bill Fold      18   Moscow
    Sue Doe-Nymm   25   Athens
    Ben Dover      22   Helsinki
    Bill Fold      18   Moscow
    Polly Wogg     19   Helsinki
    

    Notice that the first and fourth rows are identical. Here’s the UPDATE command to change the name in only one of them.

    UPDATE qtemp.mydata AS a
       SET NAME = 'Sam O''Var'
     WHERE rrn(a) =
           (SELECT MAX(rrn(b))
              FROM qtemp.mydata AS b
             WHERE NAME = 'Bill Fold')
    

    Let’s see what’s taking place. Notice first the subquery in the last three lines. It returns the relative record number of the last row with Bill Fold in the name column. (If you prefer to update the first of the duplicate rows, use the MIN function instead.) The UPDATE command changes the name in the row whose relative record number matches the relative record number returned in the subquery. Voilà! One, and only one, row updated.

    Name          Age  Location
    ============  ===  ========
    Bill Fold      18   Moscow
    Sue Doe-Nymm   25   Athens
    Ben Dover      22   Helsinki
    Sam O'Var      18   Moscow
    Polly Wogg     19   Helsinki
    

    By the way, this discussion points out yet another difference between the positioned update and the searched update, which I examined in Updating Through an SQL Cursor.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    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

    Sponsored Links

    DRV Technologies:  Automatically convert and distribute AS/400 reports with SpoolFlex
    Computer Measurement Group:  CMG '07 International Conference, December 2-7, San Diego
    COMMON:  Join us at the annual 2008 conference, March 30 - April 3, in Nashville, Tennessee

    IT Jungle Store Top Book Picks

    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Calypso Sings Praise of Inovis for EDI Database Tool Maker Joins the System i Market

    Leave a Reply Cancel reply

Volume 7, Number 42 -- December 5, 2007
THIS ISSUE SPONSORED BY:

Vision Solutions
WorksRight Software
Twin Data

Table of Contents

  • Create Multiple Directory Levels in One Swell Foop
  • Update Only One of Duplicate Records
  • Admin Alert: Getting Started with Trial Capacity on Demand, Part 1

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