Justin Justin - 6 months ago 20
SQL Question

Update SQL column with sequenced number

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?

Answer

Try this:

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.

Attention: Look carefully what you are ordering for. I used NUMBER but you might need some other sort column (maybe your IDENTITY column)

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
Comments