How to update data in batches by MySQL

This article mainly explains "MySQL how to update data in batches". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "MySQL how to update data in batches"!

Method 1: Case... when... then... end

MySQL implements one sql to complete multiple data updates

UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END, title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' WHEN 3 THEN 'New Title 3' ENDWHERE id IN (1,2,3)

Method 2: ON DUPLICATE KEY UPDATE Usage (primary key index or unique index conflict needs to be used to decide whether to insert or update)

mysql implements upsert

insert into yundou_management.statistic_customer(customer_id,current_period,period_number,client_upload_bill,update_time) values(1,201604,100,100,1540470512),(314,201604,100,100,1540470512),(315,201604,100,100,1540470512),(316,201611,100,100,1540470512)ON DUPLICATE KEY UPDATE customer_id=values(customer_id),current_period=values(current_period),period_number=values(period_number),client_upload_bill=values(client_upload_bill),update_time=values(update_time)

Method 3: Replace into

MySQL replace into

This method has limitations: it can only be updated if there is a primary key field in the write field.

At this point, I believe that everyone has a deeper understanding of "MySQL how to update data in batches", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!