mysql - 如果在分组依据中至少填充了一个，则更新列

Question

我正在尝试更新 col_a 以用“App”填充空列，如果其中至少有一个填充有“App"按 col_b

分组

我很难思考如何做到这一点。

现状:表A

+----+--------------+------+
|id  | col_a        | col_b|
+----+--------------+------+
| 1  | App          | 1111 |
+----+--------------+------+
| 2  | NULL         | 1111 |
+----+--------------+------+
| 3  | NULL         | 1111 |
+----+--------------+------+
| 4  | App          | 1111 |
+----+--------------+------+
| 5  | Tus          | 2222 |
+----+--------------+------+
| 6  | NULL         | 2222 |
+----+--------------+------+
| 7  | Hoe          | 2222 |
+----+--------------+------+
| 8  | NULL         | 2222 |
+----+--------------+------+
| 9  | App          | 3333 |
+----+--------------+------+
| 10 | NULL         | 3333 |
+----+--------------+------+
| 11 | App          | 3333 |
+----+--------------+------+
| 12 | NULL         | 3333 |
+----+--------------+------+

期望的结果:表A

+----+--------------+------+
|id  | col_a        | col_b|
+----+--------------+------+
| 1  | App          | 1111 |
+----+--------------+------+
| 2  | App          | 1111 |
+----+--------------+------+
| 3  | App          | 1111 |
+----+--------------+------+
| 4  | App          | 1111 |
+----+--------------+------+
| 5  | Tus          | 2222 |
+----+--------------+------+
| 6  | NULL         | 2222 |
+----+--------------+------+
| 7  | Hoe          | 2222 |
+----+--------------+------+
| 8  | NULL         | 2222 |
+----+--------------+------+
| 9  | App          | 3333 |
+----+--------------+------+
| 10 | App          | 3333 |
+----+--------------+------+
| 11 | App          | 3333 |
+----+--------------+------+
| 12 | App          | 3333 |
+----+--------------+------+

Answer 1

update your_table_name
set col_a = 'App'
where col_b IN (select col_b from your_table_name where col_a = 'App')

过滤所有 col_b 值并仅更新过滤值中存在 col_b 值的值。

您可以使用 is null 以这种方式进一步优化它:

update your_table_name
set col_a = 'App'
where col_a IS NULL and col_b IN (select col_b from your_table_name where col_a = 'App')

#更新:

• 上述查询抛出一个错误，相同的表名在 MySQL 中被提及两次。

方法一:

为避免此问题，我们可以创建表名的别名并按如下所示对其进行调整:

update your_table_name
set col_a = 'App'
where col_a IS NULL and col_b IN (select col_b from (select * from your_table_name) t2 where col_a = 'App')

方法二:

我们可以对表进行内部连接，并将包含 App 的 col_b 值分组，然后相应地更新行。这将提高性能。

update your_table_name t1
inner join (
    select col_b
    from your_table_name
    where col_a = 'App'
    group by col_b
    having count(col_b) > 0
) t2
on t1.col_b = t2.col_b 
set t1.col_a = 'App'