Question 137801 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – update sql rows with random values in a given range

I am trying to create an SQL event that updates n rows every 5 minutes in a table where id between 1 and 100. However I would like the query not to repeat the same rows on each update; for example if I update rows 1 to 10, the next update will be different e.g rows 29 to 39 etc. This is what I have but I do not know how to select random ranges to update e.g 1-10, 12-22 etc. How can I achieve this?

UPDATE table 
  SET timer = CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 245) SECOND
WHERE id > 1 AND id < 12;
Tags:

2

Answers


  1. 0

    Add a column with the last update time of the row, and check that the time since the last update is more than a threshold.

    UPDATE table 
  SET timer = CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 245) SECOND,
      timer_updated = CURRENT_TIMESTAMP
WHERE timer_updated < CURRENT_TIMESTAMP - INTERVAL 1 HOUR
ORDER BY RAND()
LIMIT 10;

    You could make the range of ids a function of the time.

    UPDATE table 
SET timer = CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 245) SECOND
WHERE id BETWEEN FLOOR(100 * MINUTE(NOW) / 60) AND FLOOR(100 * (MINUTE(NOW) + 5) / 60) - 1

    This splits the hour into 12 approximately equal ID ranges, and updates a different range every 5 minutes.

    Login or Signup to reply.
  2. 0

    use a transaction and a lock to update non-overlapping ranges of IDs, while also checking for conflicts with other updates:

    -- start a transaction and lock the table
START TRANSACTION;
LOCK TABLES table WRITE;

-- select 10 random non-overlapping ranges of IDs that have not been updated in the past
SELECT @start_id := start_id
FROM (
  SELECT FLOOR(RAND() * (91-1) + 1) AS start_id
  FROM table
  WHERE NOT EXISTS (
    SELECT 1 FROM table WHERE timer >= NOW() - INTERVAL 5 MINUTE AND id BETWEEN start_id AND start_id + 9
  )
  ORDER BY RAND()
  LIMIT 10
) AS random_ranges
ORDER BY start_id;

-- update the selected ranges with new timer values, while checking for conflicts with other updates
UPDATE table 
SET timer = CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 245) SECOND
WHERE id BETWEEN @start_id AND @start_id + 9
  AND NOT EXISTS (
    SELECT 1 FROM table WHERE timer >= NOW() - INTERVAL 5 MINUTE AND id BETWEEN @start_id AND @start_id + 9 AND id <> OLD.id
  );

-- commit the transaction and release the lock
COMMIT;
UNLOCK TABLES;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search