Justin - 1 year ago 73

SQL Question

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.

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

Source (Stackoverflow)