Update column with certain value to a column value

问题:

My Table:

 ID | Number
-------------------
| 1 | 0x
| 1 | 12345678
| 1 | 12345678
| 2 | 0x
| 2 | 0x
| 2 | 242424
| 3 | 88888
| 3 | 88888
| 4 | 0x
| 4 | 0x

Table must be updated so that every '0x' will be updated to a correct 'Number' if there exists one.

Result needed:

ID | Number
-------------------
| 1 | 12345678      <-- Updated
| 1 | 12345678
| 1 | 12345678
| 2 | 242424        <-- Updated
| 2 | 242424        <-- Updated
| 2 | 242424
| 3 | 88888         <- No change on id = 3
| 3 | 88888         <- No change on id = 3
| 4 | 0x            <- No change because there's no relevant 'Number' to update 
| 4 | 0x            <- No change because there's no relevant 'Number' to update 

回答1:

Demo of the following query

;WITH CTE AS (
    SELECT Id, Max(number) Number
    FROM YourTable
    WHERE Number <> '0x'
    GROUP BY Id
)
UPDATE ut SET Number = c.Number
FROM YourTable ut
    JOIN CTE c ON ut.Id = c.Id
WHERE ut.Number = '0x'