来自同一表的MYSQL更新 [英] MYSQL Update from within same table

问题描述

我有一个表，其中包含一些出错的数据，需要修复.下面是一个示例:

I have a table with some data that has gone wrong, that I need to fix up. Below is an example:

TABLE-A
id, type, value
1, 10, 123456
2, 10, NULL
3, 10, NULL
4, 20, 123456
5, 20, 654321
6, 20, NULL

我需要一个MYSQL更新命令.

I need a MYSQL update command.

如果类型"相同，则更新值"，因此只要值是NULL并且值"是唯一的，它就相同

If the "type" is the same then update the "value" so it is the same as long as the value is NULL and the "value" is unique

UPDATE table-a SET value = (...)

因此在上表中，只有id 2和3的值将更新为123456

So in the table above only id 2 and 3 will have the value updated to 123456

id 6不会更新，因为值"对于相同的类型"不是唯一的.

id 6 will not update as the "value" is not unique for the the same "type".

推荐答案

UPDATE TABLE_A t
         JOIN
           ( SELECT type
                  , MIN(value) AS value
             FROM TABLE_A
             GROUP BY type
             HAVING COUNT(DISTINCT value) = 1
           ) AS tu
         ON tu.type = t.type
SET t.value = tu.value
WHERE t.value IS NULL

正如Peufeu所指出的，需要DISTINCT来捕获这种情况，我想id = 3行也必须更新:

As Peufeu pointed, the DISTINCT is needed to catch cases like this one, where I suppose the id=3 row has to be updated, too:

TABLE-A
id | type | value
 1 |  10  | 123456
 2 |  10  | 123456
 3 |  10  | NULL
 4 |  20  | 123456
 5 |  20  | 654321
 6 |  20  | NULL

这篇关于来自同一表的MYSQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！