Re: Another SQL Question

Elvis Budimlic wrote:

On the second look, my update can be simplified a bit:

UPDATE myFile A SET A.SEQ_# = (SELECT ORDINAL_NUMBER FROM (SELECT FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE, ROW_NUMBER() OVER(ORDER BY FISCAL_YEAR) AS ORDINAL_NUMBER
FROM myFile
GROUP BY FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE) B
WHERE A.FISCAL_YEAR = B.FISCAL_YEAR AND A.CONTROL_GROUP = B.CONTROL_GROUP AND A.SOURCE_CODE = B.SOURCE_CODE)

Yup, this does the trick. You can use the ORDER BY clause to change the sequence the numbers are incremented, and it updates perfectly. I have to remember the UPDATE using a subselect technique.

Thanks, Elvis! (Another one of those folks who have forgotten more than I know about RDBMS.)

Joe