i've something similar to 40K rows and that i must run update query such as this:
UPDATE table1 SET column1='very long value 1...', column2='normal lenght value 1a' WHERE pid ='123' column3='ccc';
column1 is TEXT
column2 is Varchar(150)
...and i wish to use mysql query furthermore server resource friendly.
I wish to use something of that nature:
UPDATE table1 CASE pid WHEN '123' THEN 'very long value 1...' WHEN '124' THEN 'very long value 2...' WHEN '543' THEN 'very long value 4...' ... WHEN pid='34532' THEN 'very long value 5...' ELSE column1 END, column2= CASE pid WHEN '123' THEN 'normal lenght value 1a' WHEN '124' THEN 'normal lenght value 2a' WHEN '543' THEN 'normal lenght value 4a' ... WHEN pid='34532' THEN 'normal lenght value 5a' ELSE column2 END WHERE pid IN ('123','124','543', ...,'34532') AND column3='ccc';
and my real question is the number of rows i'm able to update in by doing this in a single query?
Can there be other server resource friendly approach to update this 40K rows?
i believe that it doesn't rely on the amount of rows however the query size !
how big one query its depend within the parameter
to improve this u look at :