informix

Update rows in an Informix database by incrementing serial numbers


I have a table in an Informix database, let's say it is called store, that looks like:

serial_num store_seq
381-15 82600
381-15 82610
381-15 82640
381-15 82650
381-15 82680
381-114-1 82690

I have to update serial_num so that numbers are updated incrementally like this:

serial_num store_seq
381-15-1 82600
381-15-2 82610
381-15-3 82640
381-15-4 82650
381-15-5 82680
381-114-1 82690

Transcribing a comment into the question:

I tried creating a procedure:

CREATE PROCEDURE update_store()
    DEFINE counter INTEGER;
    DEFINE temp_serial_num CHAR(20);
    FOREACH cur1 FOR
        SELECT serial_num INTO temp_serial_num FROM store WHERE serial_num = '381-15'
        LET counter = 0;
        WHILE counter < (SELECT COUNT(*) FROM store WHERE serial_num = '381-15')
            LET counter = counter + 1;
            UPDATE store
               SET serial_num = temp_serial_num || '-' || counter::CHAR
             WHERE CURRENT OF cur1;
         END WHILE;
     END FOREACH;
END PROCEDURE;

The result was 381-15-5 in all 381-15 numbers.

NB: The serial_num column is a CHAR(16) column; store_seq is an INTEGER column.


Solution

  • After fixing up some name mismatches between the code in the comment and the schema of the table, I find your stored procedure doesn't change the data. However, that's in part because you don't use TRIM to remove the trailing blanks from serial_num before appending the new counter. The TRIM would not be necessary if the serial_num column was a VARCHAR instead of a CHAR, but it would do no harm beyond slowing things down a little.

    I added an Informix SERIAL column rec_num to the table:

    DROP TABLE IF EXISTS store;
    
    CREATE TABLE store
    (
        recnum       SERIAL(1000) NOT NULL PRIMARY KEY,
        serial_num   CHAR(16) NOT NULL,
        store_seq    INTEGER NOT NULL
    );
    
    INSERT INTO store VALUES(0, '381-15', '82600');
    INSERT INTO store VALUES(0, '381-15', '82610');
    INSERT INTO store VALUES(0, '381-15', '82640');
    INSERT INTO store VALUES(0, '381-15', '82650');
    INSERT INTO store VALUES(0, '381-15', '82680');
    INSERT INTO store VALUES(0, '381-114-1', '82690');
    

    A modified procedure takes the serial_num to fix as a parameter:

    DROP PROCEDURE IF EXISTS update_store;
    
    CREATE PROCEDURE update_store(snum CHAR(16))
        DEFINE counter INTEGER;
        DEFINE temp_serial_num CHAR(20);
    
        TRACE ON;
        FOREACH cur1 FOR
            SELECT serial_num INTO temp_serial_num FROM store WHERE serial_num = snum
            LET counter = 0;
            WHILE counter < (SELECT COUNT(*) FROM store WHERE serial_num = snum)
                LET counter = counter + 1;
                UPDATE store
                   SET serial_num = TRIM(temp_serial_num) || '-' || counter::CHAR
                 WHERE CURRENT OF cur1;
             END WHILE;
         END FOREACH;
    END PROCEDURE;
    

    When run, I get the output:

    recnum serial_num store_seq
    1005 381-114-1 82690
    1003 381-15-1 82650
    1004 381-15-1 82680
    1002 381-15-2 82640
    1001 381-15-3 82610
    1000 381-15-4 82600

    This changes the data in the table, but because no ordering was applied, the rows are not updated in the order of the store_seq value, which it appears is the desired result.

    A rewritten procedure deals with the ordering problem:

    DROP PROCEDURE IF EXISTS update_store;
    
    CREATE PROCEDURE update_store(snum CHAR(16))
    
        DEFINE counter INTEGER;
        DEFINE old_serial_num CHAR(20);
        -- DEFINE new_serial_num CHAR(20);
        DEFINE old_store_seq INTEGER;
    
        -- TRACE ON;
    
        LET counter = 0;
        FOREACH cur1 FOR
            SELECT serial_num, store_seq
          INTO old_serial_num, old_store_seq
          FROM store
         WHERE serial_num = snum
         ORDER BY serial_num, store_seq
            LET counter = counter + 1;
            -- LET new_serial_num = TRIM(old_serial_num) || '-' || counter;
            UPDATE store
               SET serial_num = TRIM(old_serial_num) || '-' || counter
             WHERE serial_num = old_serial_num AND store_seq = old_store_seq;
         END FOREACH;
    
    END PROCEDURE;
    

    The new_serial_num variable (and the TRACE ON; statement) helped identify the missing TRIM problem.

    This produces the output:

    recnum serial_num store_seq
    1005 381-114-1 82690
    1000 381-15-1 82600
    1001 381-15-2 82610
    1002 381-15-3 82640
    1003 381-15-4 82650
    1004 381-15-5 82680

    You can't apply a WHERE CURRENT OF cur1 clause to an UPDATE when the cursor has an ORDER BY clause on it. That's why the processing identifies the row to be updated by serial_num and store_req.