Mysql Multiple Bulk Update

Question

I have issues with the time it takes MySQL to update multiple rows, see example below:

update sub set rn='Q',bdate=now() where id='43721';
update sub set rn='Q',bdate=now() where id='905666';
update sub set rn='Q',bdate=now() where id='356748';
update sub set rn='Q',bdate=now() where id='57848';
update sub set rn='Q',bdate=now() where id='25359';
update sub set rn='Q',bdate=now() where id='100060';

Can anyone suggest a faster way to do this? I have 100,000 records to update in this format.

I update the records with the following command:

mysql -u root -pXxXX XXXXX -h xx.xx.xx.xx < /root/code/sub01.txt

Michael

Answer 1

You can use WHERE id IN (<list of ids>) to do them all at once.

UPDATE sub 
SET rn = 'Q', bdate = NOW()
WHERE id IN (43721, 905666, 356748, ...)