MySQL 使用同一表中另一行的数据更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5574434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update row with data from another row in the same table
提问by slick
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 |
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:
我正在寻找一种方法来更新值 'Test2' 和 'Test' 与来自'VALUE' 列中具有相同'NAME' 的其他行的数据（ID 在这里不是唯一的，ID 的复合键和 NAME 使行唯一）。例如，我正在寻找的输出是：
ID | NAME | VALUE |
----------------------------
1 | Test | VALUE1 |
2 | Test2 | VALUE2 |
1 | Test2 | VALUE2 |
4 | Test | VALUE1 |
1 | Test3 | VALUE3 |
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.
如果它在另一个表中，我会很好，但是我不知道如何使用相同的 NAME 值引用当前表中的不同行。
Update
更新
After modifying manji query, below is the query I used for a working solution. Thanks all!
修改 manji 查询后，下面是我用于工作解决方案的查询。谢谢大家！
UPDATE data_table dt1, data_table dt2
SET dt1.VALUE = dt2.VALUE
WHERE dt1.NAME = dt2.NAME AND dt1.VALUE = '' AND dt2.VALUE != ''
回答by manji
Try this:
尝试这个：
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
回答by Gustav Larsson
Here's my go:
这是我的做法：
UPDATE test as t1
INNER JOIN test as t2 ON
t1.NAME = t2.NAME AND
t2.value IS NOT NULL
SET t1.VALUE = t2.VALUE;
EDIT: Removed superfluous t1.id != t2.idcondition.
编辑：删除了多余的t1.id != t2.id条件。
回答by Thomas
Update MyTable
Set Value = (
Select Min( T2.Value )
From MyTable As T2
Where T2.Id <> MyTable.Id
And T2.Name = MyTable.Name
)
Where ( Value Is Null Or Value = '' )
And Exists (
Select 1
From MyTable As T3
Where T3.Id <> MyTable.Id
And T3.Name = MyTable.Name
)
回答by Prashant Khunte
UPDATE financialyear
SET firstsemfrom = dt2.firstsemfrom,
firstsemto = dt2.firstsemto,
secondsemfrom = dt2.secondsemfrom,
secondsemto = dt2.secondsemto
from financialyear dt2
WHERE financialyear.financialyearkey = 141
AND dt2.financialyearkey = 140
回答by Ishan Liyanage
If you just need to insert a new row with a data from another row,
如果您只需要插入包含另一行数据的新行，
insert into ORDER_ITEM select * from ORDER_ITEM where ITEM_NUMBER =123;
