Adrian P. Adrian P. - 7 months ago 20
SQL Question

mySQL update next 12 NULL values with date increment by 1

Basically, I have these 2 queries:

SELECT * FROM table
WHERE langue = 'fr' AND hDate IS NULL
LIMIT 12;

UPDATE table
SET hDate = CURDATE() + INTERVAL 1 DAY
WHERE hDate IS NULL
LIMIT 12;


These works good for first 12 NULL records. If I need to update next 12 NULL records I have to manually change the UPDATE query to INTERVAL 2 DAY

Problem is that I have 4000 records.

I have tried

UPDATE table t1 JOIN
(
SELECT id, @n := @n + 1 rnum
FROM table CROSS JOIN (SELECT @n := 0) i
WHERE langue = 'fr'
ORDER BY id
) t2 ON t1.id = t2.id CROSS JOIN
(
SELECT MAX(hDate) sdate FROM table
) q
SET t1.hDate = q.sdate + INTERVAL t2.rnum DAY


from this answer: MySQL query to update records with incremented date

but this increments each record with 1 day. I have to increment 12 records with same date, next 12 records with date + 1, next 12 records with date + 2 etc.

Table definition

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`texte` mediumtext,
`langue` varchar(9) DEFAULT NULL,
`hDate` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6726 DEFAULT CHARSET=utf8;


Thanks for your help.

Answer

http://sqlfiddle.com/#!9/7a554/1

SET @i:=0;
SET @j:=0;

UPDATE t1

RIGHT JOIN (
  SELECT 
     id
     ,IF(@j = 0 ,@j:=1, @j:=@j+1)
     ,IF((@j-1) % 12 = 0, @i:= @i+1, @i)  as i
  FROM t1
  WHERE hDate IS NULL
) idx
on idx.id = t1.id
  SET t1.hDate = CURDATE() + INTERVAL (idx.i)  DAY