WITH RowNumbers AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY G TLINE) AS row_num,
gtline, gttran
FROM
GPOTRSP
)
UPDATE b
SET b.gtline = a.row_num
FROM GPOTRSP b
JOIN RowNumbers a ON b.gtline <> a.gtline
AND b.gttran = a.gttran
I want to resequence the line no after deletion on one record.
Direct update with CTE (WITH) is not supported in DB2 for i.
you can store the results in a temporary table and update GPOTRSP.
Instead of using a JOIN, updates each row by matching its gtline and gttran in the subquery that calculates new row numbers.
Try this ......
DECLARE GLOBAL TEMPORARY TABLE TEMP_GTLINE AS (
SELECT ROW_NUMBER() OVER (PARTITION BY gttran ORDER BY gtline) AS new_gtline,
gtline,
gttran
FROM GPOTRSP
) WITH DATA;
UPDATE GPOTRSP AS b
SET gtline = (SELECT new_gtline
FROM SESSION.TEMP_GTLINE AS a
WHERE a.gttran = b.gttran
AND a.gtline = b.gtline);
