I have a table SL_PROD which has the following columns, NUMBER, DEPTCODE, DISP_SEQ AND SL_PROD_ID.
SL_PROD_ID is an identity column which incrementally increases with each row.
I need to write a query which updates the DISP_SEQ column with sequential numbers (1-X) for the rows which have a DEPTCODE of '725'. I've tried several things with no luck, any ideas?
A common table expression can be used in updates. This is extremely usefull, if you want to use the values of window functions (with
OVER) as update values.
NUMBERbut you might need some other sort column (maybe your
CREATE TABLE #SL_PROD(NUMBER INT,DEPT_CODE INT,DISP_SEQ INT,SL_PROD_ID INT IDENTITY); INSERT INTO #SL_PROD(NUMBER,DEPT_CODE,DISP_SEQ) VALUES (1,123,0) ,(2,725,0) ,(3,725,0) ,(4,123,0) ,(5,725,0); WITH UpdateableCTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY NUMBER) AS NewDispSeq ,DISP_SEQ FROM #SL_PROD WHERE DEPT_CODE=725 ) UPDATE UpdateableCTE SET DISP_SEQ=NewDispSeq; SELECT * FROM #SL_PROD; GO --Clean up --DROP TABLE #SL_PROD;
The result (look at the lines with 725)
1 123 0 1 2 725 1 2 3 725 2 3 4 123 0 4 5 725 3 5