将具有特定值的列更新为具有相同 id 的列值 [英] Update column with certain value to a column value with same id

问题描述

我的表:

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

必须更新表，以便每个0x"都将更新为正确的数字"(如果存在).

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

需要的结果:

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 

推荐答案

以下查询的演示

;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'

这篇关于将具有特定值的列更新为具有相同 id 的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！