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.