MySQL update row cell with 0 value with value from closest row that does not have 0 value 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` &gt; 0
   SET `m`.`time` = `s`.`time`
 WHERE `m`.`time` = 0


My schema.

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
SET @var :=0;
UPDATE records
SET time= (@var :=CASE WHEN time = '0' THEN @var ELSE time END)
ORDER BY id;


Fiddle

Add DESC after ORDER BY if you want to update time with the time value of the next id.