UPDATE multiple rows with random values from array?

SQL++
1

Howdy! With the inability to use LET in an update/upsert query, and the inability to create temp tables, is there any suggestion on how to update multiple documents with random values? Pseudo code of what I’m attempting to accomplish:

UPDATE vehicle SET color=all_colors[FLOOR(RANDOM() * 3)]
LET all_colors = ['Red', 'Blue', 'Yellow']
WHERE vehicle_stage='paint'

Obviously, this doesn’t work due to LET not being available. Suggestions? Thanks!

2

Hi @R3ason ,

Inline the variable

UPDATE vehicle SET color=['Red', 'Blue', 'Yellow'][FLOOR(RANDOM() * 3)]
WHERE vehicle_stage='paint'

OR

UPDATE vehicle SET color= CASE FLOOR(RANDOM()*3) WHEN 0 THEN "RED" WHEN 1 THEN "BLUE" ELSE "YELLOW" END
WHERE vehicle_stage='paint'

MB-47277

3

Yep, works like a charm. Thanks for clearing the brain fog, @vsr1