Update SQL column using Rank() function

Question

I have a table with existing data. For each unique value in the first column of this table, we have a column that is supposed to be in sequential order, but this table has gotten out of order. I want to run a SQL statement that will put this second column back in order. I was able to see the results I want with this SQL:

select FORMULA_ID, ATTRIB_CODE, ATTRIB_VAL, ATTRIB_ORDER,
rank() over (partition by formula_id order by attrib_code, attrib_val) AS WANT_THIS
from ATTRIB

Which yields:

 FORMULA_ID  ATTRIB_CODE          ATTRIB_VAL       ATTRIB_ORDER WANT_THIS
 ----------- -------------------- ---------------- ------------ ---------
 2791        C_BRAND              ROMAN HOLIDAY    3            1
 2791        C_ENDUSE             DINNER           4            2
 2791        C_ENDUSE             SNACK            6            3
 2791        C_ENDUSER            10-17            7            4
 2791        C_PRODTYPE           SALAD            13           5
 2791        C_RELIG              ANY              14           6
 2821        C_ALLERGEN           PEANUT           1            1
 2821        C_ALLERGEN           SOY              2            2
 2821        C_BRAND              ROMAN HOLIDAY    1            3
 2821        C_ENDUSE             DINNER           1            4

As you can see, the WANT_THIS column orders the rows and resets to 1 when it gets to a new FORMULA_ID. But I don't know how to convert this into an UPDATE statement that will actually put the value in WANT_THIS into the column ATTRIB_ORDER. Is there a way to convert the SQL above into an UPDATE statement?

Answer 1

This is one way:

WITH CTE AS
(
    SELECT  FORMULA_ID, 
            ATTRIB_CODE, 
            ATTRIB_VAL, 
            ATTRIB_ORDER,
            RANK() OVER (PARTITION BY formula_id 
                         ORDER BY attrib_code, attrib_val) AS WANT_THIS
    FROM ATTRIB
)
UPDATE CTE
SET ATTRIB_ORDER = WANT_THIS;