Last Updated: February 25, 2016

MySQL for averaging missing dates

The Problem

I was looking to update rows where the date column was NULL by averaging the dates of the nearest id before and nearest id after that row.

In the data below, it would update the row with ID 26 to be 2011年10月19日

The data looks something like this:

id date title
--------------------------------------------
12 2011年09月01日 Example One
23 2011年10月02日 Example Two
26 NULL Example Three
27 2011年11月05日 Example Four
29 2012年01月05日 Example Five
37 NULL Example Six
38 2012年02月03日 Example Seven
--------------------------------------------

The Solution

UPDATE tableX AS t
 JOIN tableX AS next
 ON next.id =
 ( SELECT MIN(n.id)
 FROM tableX AS n
 WHERE n.id > t.id
 AND n.dateX IS NOT NULL
 )
 JOIN tableX AS prev 
 ON prev.id =
 ( SELECT MAX(p.id)
 FROM tableX AS p
 WHERE p.id < t.id
 AND p.dateX IS NOT NULL
 )
SET t.dateX = prev.dateX + INTERVAL (DATEDIFF(next.dateX, prev.dateX) / 2) DAY
WHERE t.dateX IS NULL

Thanks to ypercube for helping figure this one out!

#mysql