• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Use SQL To Update A Sequence Number

    November 14, 2012 Hey, Ted

    Is it possible to use a single SQL statement to assign an ascending sequence number to a column in a table? I’d like the sequence number to start at 10 and increment by 10 as every row is updated so that the number column in the updated rows would be 10, 20, 30, etc.

    –Doug

    I know a way, Doug. However, let me say up front that I’ve only played with this. That is, I’ve never used it in a production environment. I can’t speak to how practical it might be or what you might need to watch out for.

    DB2 for i provides a way to create an object that generates a sequence of numbers. You can use this object to update your database table (physical file). Here’s an example.

    First, create a table to play with.

    create table TestSeq
    (SerialNbr dec(5,0), Name char(24))
    

    Next, put some data into the table.

    insert into testseq (Name) values 
    ('Bob White-Quayle'),
    ('Billy Doo'),
    ('Jack O''Napes')
    

    Here’s what the data looks like.




    SERIALNBR

    SERIALNBR

    NAME

    Null

    Bob White-Quayle

    Null

    Billy Doo

    Null

    Jack O’Napes

    Create the sequence generator.

    create sequence renumber
    start with 10
    increment by 10
    no maxvalue
    no cycle
    

    Update your table.

    update testseq
       set SerialNbr = next value for renumber
    

    Take another look at the data.




    SERIALNBR

    SERIALNBR

    NAME

    10

    Bob White-Quayle

    20

    Billy Doo

    30

    Jack O’Napes

    If you’re finished with the sequence generator, you can get rid of it.

    drop sequence renumber
    

    If not, you can leave it around for next time. The sequence will start where it left off.

    Maybe that’s an answer to your question.

    You can also use a sequence generator when adding data to a table.

    insert into testseq (SerialNbr, Name) values
    (next value for renumber, 'Bob White-Quayle'),
    (next value for renumber, 'Billy Doo'),
    (next value for renumber, 'Jack O''Napes')
    

    This looks like a good way to generate unique key values. Maybe I’ll get a chance to use it for real someday.

    –Ted

    RELATED STORY

    V5R3 Advances DB2 UDB for iSeries



                         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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    HiT Software:  Download FREE paper "Change Data Capture for Business Intelligence and Analytics"
    looksoftware:  Achieving the impossible with RPG Open Access. Live webcast Dec 4 & 5.
    ITJ Bookstore:  Bookstore BLOWOUT!! Up to 50% off all titles! Everything must go! Shop NOW

    IT Jungle Store Top Book Picks

    Bookstore Blowout! Up to 50% off all titles!

    The iSeries Express Web Implementer's Guide: Save 50%, Sale Price $29.50
    The iSeries Pocket Database Guide: Save 50%, Sale Price $29.50
    Easy Steps to Internet Programming for the System i: Save 50%, Sale Price $24.97
    The iSeries Pocket WebFacing Primer: Save 50%, Sale Price $19.50
    Migrating to WebSphere Express for iSeries: Save 50%, Sale Price $24.50
    Getting Started with WebSphere Express for iSeries: Save 50%, Sale Price $24.50
    The All-Everything Operating System: Save 50%, Sale Price $17.50
    The Best Joomla! Tutorial Ever!: Save 50%, Sale Price $9.98

    IBM Deals On PowerSC Security Wares, And IBM i Hooks In LANSA Launches LongRange University

    Leave a Reply Cancel reply

Volume 12, Number 27 -- November 14, 2012
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
PowerTech

Table of Contents

  • Converting CASE in CL
  • Use SQL To Update A Sequence Number
  • Admin Alert: A Checklist For Performing IBM i Planned Maintenance

Content archive

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

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

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