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 Status1 Null
2 Added
3 Null
4 Null
5 Changed
6 Null
7 Null
What I want to achieve is this:
RefID Status1 Null
2 Added
3 Added
4 Added
5 Changed
6 Changed
7 Changed
解决方案
Try this query -
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