What is the method for updating data in MySQL in bulk?

2 years ago
Benjamin Taylor
1 minute

There are three ways to update data in MySQL in batch:

  1. By using the UPDATE … SET … WHERE statement, you can specify multiple update operations in a single UPDATE statement. For example, you can use multiple conditions and multiple SET clauses to update multiple rows of data. For instance:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  1. By using the INSERT … ON DUPLICATE KEY UPDATE statement, you can update or insert data if there is a conflicting unique key or primary key in the table. For example:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2, ...;
  1. You can utilize the LOAD DATA INFILE statement to efficiently update a large amount of data by storing it in a file and then loading the file into a table. For example:
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(column1, column2, ...);

The above are common methods for bulk updating data, the specific method used depends on the amount of data and requirements.

bannerAds