Mysql用前一条记录中的值更新一条记录 [英] Mysql Updating a record with a value from the previous record

问题描述

我有下表，并且我想做的是更新，例如，具有与先前值相同的状态值已添加"的RefID 3-14.我要这样做，直到达到下一个完成的值.因此，当遇到已添加"时，它将使用已添加"更新下面所有具有空值的行，直到到达具有填充状态的下一个RefID，然后更新下一批值.能做到吗?任何帮助将不胜感激.我希望这是动态的，因为我不想手动更新，例如，StatID ="Added"，其中RefID为3.

I have the following table, and what I'm trying to do is to update, for example, RefIDs 3-14 with the same Status value "Added" as the previous value. I want to do this until it reaches the next completed value. So when it encounters "Added", it updates all rows below that have a null with "Added" until it reaches the next RefID that has a populated Status, and subsequently updates the next batch of values. Can this be done? Any help would be greatly appreciated. I'm looking for this to be dynamic, as I don't want to manually update, say, Status = "Added" where RefID is 3.

RefID Status


1 Null

RefID Status


1 Null
2 Added
2 Added
3 Null
3 Null
4 Null
4 Null
5 Changed
5 Changed
6 Null
6 Null
7 Null
7 Null

我想要实现的是:

RefID Status


1 Null

RefID Status


1 Null
2 Added
2 Added
3 Added
3 Added
4 Added
4 Added
5 Changed
5 Changed
6 Changed
6 Changed
7 Changed
7 Changed

推荐答案

尝试此查询-

UPDATE refs_table t1
  JOIN (
    SELECT RefID, @s:=IF(Status IS NULL, @s, Status) Status
      FROM (SELECT * FROM refs_table ORDER BY RefID) r,
    (SELECT @s:=NULL) t
  ) t2
  ON t1.RefID = t2.RefID
SET t1.Status = t2.Status

这篇关于Mysql用前一条记录中的值更新一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！