Use ROW_NUMBER to increase by 100?
source link: https://www.codesd.com/item/use-row-number-to-increase-by-100.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Use ROW_NUMBER to increase by 100?
I have an existing table where a line sequence number is incremented by 100 in normal software operation. I need to perform a large insert and maintain this method of incrementation. I thought I would use ROW_NUMBER() but I do not know if it can increment in this way.
I'd appreciate help on that simple topic, however, if you want to get more specific please also note that the line sequence is unique for a Project and some records already started the sequence. For example
Project Category Sequence
Proj1 Cat4 100
Proj1 Cat2 200
Proj2 Cat7 100
Proj2 Cat1 200
Proj3 Cat1 100
So if these exist already and I am inserting new records, I have to account for the sequence already started for each project. So if I insert rows for Proj1, the Sequence value must be 300, 400, ...
Here is my block of code so far:
WITH CTE (PROJECTNUM, COSTCATID, LINE) AS
( SELECT PROJECTNUM, PACOSTCATID, ROW_NUMBER() OVER(PARTITION BY PROJECTNUM ORDER BY PACOSTCATID)
+ (SELECT MAX(LNITMSEQ) FROM PROJECTTABLE WHERE PAPROJNUMBER = A.PROJECTNUM)
FROM PROJECTSHORTLIST A, CATEGORYTABLE
WHERE <stuff you don't care about>
)
INSERT INTO PROJECTTABLE
(PAPROJNUMBER, PACOSTCATID, LNITMSEQ)
SELECT PROJECTNUM, COSTCATID, LINE
FROM CTE;
You can see this will produce records incrementing by one and for Proj1 would be 301, 302.
Your brain power is greatly appreciated.
Update: I will add that if this incrementation by 100 can be more easily done in an update statement after the rows are first inserted that is perfectly acceptable. Thank you.
I'm answering my own question because 5 minutes after asking it, I thought of a solution. I moved the use of the "SELECT MAX()" portion down to the INSERT statement, leaving the row number intact for later use (I need it for additional columns). So in the SELECT portion of the INSERT I simply add ROW * 100 to my MAX sequence number.
WITH CTE (PROJECTNUM, COSTCATID, ROW) AS
( SELECT PROJECTNUM, PACOSTCATID, ROW_NUMBER() OVER(PARTITION BY PROJECTNUM ORDER BY PACOSTCATID)
FROM PROJECTSHORTLIST A, CATEGORYTABLE
WHERE <stuff you don't care about>
)
INSERT INTO PROJECTTABLE
(PAPROJNUMBER, PACOSTCATID, LNITMSEQ)
SELECT PROJECTNUM, COSTCATID, (ROW * 100) + (SELECT MAX(LNITMSEQ) FROM PROJECTTABLE WHERE PAPROJNUMBER = A.PROJECTNUM)
FROM CTE;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK