3

I have this query:

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status IN ("DROP","ERI","NRP","RPD","OKQ","PDROP","PI","RCAT")              
ORDER BY RAND() LIMIT 500;

Now because every status has a different number of occurrences in the database, "DROP" has 8917 entries, while "PI" has 59044 entries, I want to make it consistent like, for example limiting the affected rows of "DROP" to 20, while limiting "PI" to 100 for every query, so all the statuses keep consistent.

I know I can do it like this:

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status = DROP" 
ORDER BY RAND() LIMIT 20;

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status = "PI" 
ORDER BY RAND() LIMIT 100;

But I want to know if there is a way to execute ONE query that contains all the limits for each status.

Dharman
  • 30,962
  • 25
  • 85
  • 135
medk
  • 9,233
  • 18
  • 57
  • 79
  • 1) Provide complete CREATE TABLE script for your table. 2) How many rows there are in the table totally? – Akina Jul 05 '22 at 09:54
  • 2
    If you want to set a different limit for each status then I would guess that, even if it was possible to write this as a single SQL statement, this would be slower and more complex than writing individual statements – NickW Jul 05 '22 at 11:17
  • https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – Shmiel Jul 05 '22 at 13:42

0 Answers0