Trying to renumber a field in order grouping by another

Question

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful of cases due to database manipulation some records will have a duplicate sequence resulting in an inability to re-order in the UI. In other cases there are gaps in sequencing (not a severe problem). What I am looking to do is re-number these numeric fields starting from 1 but it has to group by the 'co' field and then by the existing sequence field you see below:

This is the query I ran to get the results you see:

SELECT row_number() OVER (ORDER BY C.co, T.sequence) AS RowNum, C.co, T.sequence
FROM TNode T
INNER JOIN CInfo C ON C.guidfield = T.contextID
    WHERE T.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382'
    AND T.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9'
GROUP BY C.co, T.sequence

What I am looking to achieve is this. Note how the sequence is renumbered slightly on records where it's not a straight 1, 2, 3... but it has to start from 1 again each time a new 'co' is encountered, and then also order by the existing sequence that is there :

Answer 1

For an UPDATE query you don't need to rejoin, you can update the derived table directly

UPDATE T
SET sequence = RowNum
FROM (
    SELECT
        T.*,
        row_number() OVER (PARTITION BY C.co ORDER BY T.sequence) AS RowNum
    FROM TNode T
    INNER JOIN CInfo C ON C.guidfield = T.contextID
    WHERE T.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382'
      AND T.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9'
) T

Answer 2

Nailed it! Thank you both, that was just what I needed. Using the following I was able to successfully UPDATE the sequencing in order.

UPDATE TNode SET sequence = 
(SELECT RowNum FROM
    (SELECT row_number() OVER (PARTITION BY C.co ORDER BY T.sequence) AS RowNum, nodeID
        FROM TNode T
            INNER JOIN CInfo C ON C.guidfield = T.contextID
                WHERE T.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382'
                    AND T.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9') AS ReSeq 
        WHERE ReSeq.nodeID = TNode.nodeID) 
    WHERE TNode.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382' 
        AND TNode.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9'