1

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

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
2
  • Closest row based on a primary key?
    – Mihai
    Apr 5, 2014 at 13:46
  • Yes based on primary key that is called id. Apr 5, 2014 at 13:52

1 Answer 1

0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.