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.
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
.