• Home
  • Blog

Update by random value on random rows in MySQL

Posted: November 11, 2021


tl;dr

UPDATE my_table
SET
    -- ↓ possible values are 1 ~ 190
    country_id = FLOOR(1+ rand()*190),
    -- ↓ you also can use RAND and CASE instead of ELT()
    favorite_fluit = ELT(FLOOR(1+ rand()*3), 'orange', 'apple', 'strawberry');
-- ↓  50%
WHERE RAND() < 0.5
;

ref:

  • https://stackoverflow.com/questions/11087059/mysql-how-do-i-update-50-of-the-rows-randomly-selected
  • https://stackoverflow.com/questions/24378490/update-table-with-random-values-from-given-set-of-string-values

Profile picture

Written by evalphobia who loves cloud technology and IPA🍺.

  • ← How to generate large sequential numbers list in shell
  • Synology NAS で Nintendo Switch用のマイクラサーバーをDockerでつくる →