I have a list of entries that has a time field where unix timestamp is stored. I recently discovered that some of the entries have that field value 0. I want to run an update that will update those records with the value of the closest row that does not have 0 value.
This is what I have built so far but I am unsure it will use the value of the closest record.
UPDATE `records` AS `m` JOIN `records` AS `s` ON `s`.`time` > 0 SET `m`.`time` = `s`.`time` WHERE `m`.`time` = 0
CREATE TABLE `records` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `string` VARCHAR(254) NOT NULL DEFAULT '', `time` INT(11) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`), INDEX `time` (`time`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM