使用同一表中另一行的数据更新行 [英] Update row with data from another row in the same table

问题描述

我有一张看起来像这样的桌子

I've got a table which looks something like this

ID   |   NAME    |  VALUE  |
----------------------------
 1   |   Test    |  VALUE1 |
 2   |   Test2   |  VALUE2 |
 1   |   Test2   |         |
 4   |   Test    |         |
 1   |   Test3   |  VALUE3 |

我正在寻找一种方法来更新值 'Test2' 和 'Test' 与来自'VALUE' 列中具有相同'NAME' 的其他行的数据(ID 在这里不是唯一的，复合键ID 和 NAME 使一行唯一).例如，我正在寻找的输出是:

I'm looking for a way to update the values 'Test2' and 'Test' with the data from other rows in the 'VALUE' column with the same 'NAME' (The ID is not unique here, a composite key of the ID and NAME make a row unique). For example, the output I'm looking for is:

ID   |   NAME    |  VALUE  |
----------------------------
 1   |   Test    |  VALUE1 |
 2   |   Test2   |  VALUE2 |
 1   |   Test2   |  VALUE2 |
 4   |   Test    |  VALUE1 |
 1   |   Test3   |  VALUE3 |

如果它在另一个表中，我会很好，但我不知道如何引用当前表中具有相同 NAME 值的不同行.

If it was in another table I'd be fine, but I'm at a loss as to how I can reference a different row within the current table with the same NAME value.

更新

修改 manji 查询后，以下是我用于工作解决方案的查询.谢谢大家！

After modifying manji query, below is the query I used for a working solution. Thanks all!

UPDATE data_table dt1, data_table dt2 
SET dt1.VALUE = dt2.VALUE 
WHERE dt1.NAME = dt2.NAME AND dt1.VALUE = '' AND dt2.VALUE != '' 

推荐答案

试试这个:

UPDATE data_table t, (SELECT DISTINCT ID, NAME, VALUE
                        FROM data_table
                       WHERE VALUE IS NOT NULL AND VALUE != '') t1
   SET t.VALUE = t1.VALUE
 WHERE t.ID = t1.ID
   AND t.NAME = t1.NAME

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