mysql - 使用同一个表中的数据更新表

Question

我正在尝试使用同一表中的数据更新表 path 中的 hash 和 parType 列。我可以从 SO 的答案中得到以下 sql，但更新似乎让我失败。

我在这里尝试做的是更新 hash = 0 且 parType = 0 的所有行，其中 hash = 相同的行 id 且 parType = 该行的父 id 的类型值。

如果您看到我想要的结果，您就明白了。

你能帮忙吗？别介意我的sql。

SQL

update path t join(
select t1.id, t1.type, t2.parent from path t1, path t2 where t1.parent = t2.id;
) as p on p.id=t.id set hash = t1.id and parType = t1.type;

更新前

"id"    "type"  "parent"    "hash"  "parType"
"1"     "0"     "0"         "0"     "0"
"2"     "2"     "1"         "0"     "0"
"3"     "3"     "2"         "0"     "0"
"4"     "4"     "3"         "0"     "0"

期望的结果

"id"    "type"  "parent"    "hash"  "parType" //parType = the type of the parent
"1"     "0"     "0"         "1"     "0"
"2"     "2"     "1"         "2"     "0"
"3"     "3"     "2"         "3"     "2" -> This is value from the parents type
"4"     "4"     "3"         "4"     "3"

编辑 - 这可行，但我仍然不确定这是否是正确的方法

update path a join(
select t1.id as hash, t2.type as parType from path t1 inner join path t2 on t1.parent = t2.id
) b on a.id=b.hash set a.hash = b.hash , a.parType = b.parType;

Answer 1

类似这样的吗？

UPDATE table1 t LEFT JOIN
(SELECT t1.id,t1.type FROM table1 t1 INNER JOIN table1 t2 ON t1.id>t2.id GROUP BY t1.id) as p 
ON p.id+1=t.id SET t.hash=t.id,t.parType=ifnull(p.type,0) 

SQL fiddle