MySQL update all products for created_at and updated

I have a table of 1000 products and I want to update the column "created_at" and "updated_at" for all the products but in this schema of time

batch 1 => product with id 1000, 999, 998, 997 get "created_at" and "updated_at" change to NOW() - 4 days

batch 2 => product with id 996, 995, 994, 993 get "created_at" and "updated_at" change to NOW() - 8 days

batch 3 => product with id 992, 991, 990, 989 get "created_at" and "updated_at" change to NOW() - 12 days

And so on, and if this is possible, is it possible to randomize the number of chunks li instead of every 4 products, it will be like each (2 or 3 or 4) products

All i have now is : UPDATE products SET products.created_at = CURRENT_DATE INTERVAL -4 DAY;

Thanks in advance.

CodePudding user response：

You could possibly do with a ( select union ) with join, then you can just keep adding entries as needed vs changing a query each time. Just to see the output, try the following query by itself, then you can see the context of the update query

select 1000 productid, 4 daysChanged
union select 999, 4
union select 998, 4
union select 997, 4
union select 996, 8
union select 995, 8
union select 994, 8
union select 993, 8
union select 992, 12
union select 991, 12
union select 990, 12
union select 989, 12

Then apply an update

UPDATE products 
      INNER JOIN 
      ( entire query from select above ) ThingsToUpdate
         on products.id = ThingsToUpdate.productid
   SET created_at = CURRENT_DATE   INTERVAL -ThingsToUpdate.DaysChanged DAY,
       updated_at = CURRENT_DATE   INTERVAL -ThingsToUpdate.DaysChanged DAY

But I dont know why you would want to change BOTH columns and not just the one for updated. If a product was created on a given time, why change it, but its your data.

CodePudding user response：

MySQL 8.0 answer...

WITH
  randomised AS
(
  SELECT
    *,
    (ROW_NUMBER() OVER (ORDER BY RAND())   3) DIV 4 * 4   AS offset
  FROM
    example
)
UPDATE
  example
INNER JOIN
  randomised
    ON randomised.id = example.id
SET
  example.created_at = DATE_SUB(NOW(), INTERVAL randomised.offset DAY)

ROW_NUMBER() OVER (ORDER BY RAND()) assigns every row a random position
(x 3) DIV 4 changes that from 1,2,3,4,5,6,7,8,9... to 1,1,1,1,2,2,2,2,3...
Multiply that sequence by 4 again to get 4,4,4,4,8,8,8,8,12...

Then join back and update.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=00d2f397b25206fabc1b1e17da021bbc