使用同一表中另一行的数据更新行 [英] 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 |
我正在寻找一种方法,以使用"VALUE"列中具有相同"NAME"名称的其他行中的数据更新值"Test2"和"Test"(此处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屋!