How Can I Update Multiple Database Records with a Single SQL Query?

Update Multiple Records with a Single SQL Query

Updating a large number of records in a database can be a time-consuming and repetitive task if performed individually. To streamline the process, it's possible to update multiple records simultaneously with a single SQL query.

Using Multi-Table Update Syntax

One approach is to utilize the multi-table update syntax, which allows you to join multiple tables and modify their respective columns in a single statement.

For instance, consider the following example:

UPDATE config t1 JOIN config t2
ON t1.config_name = 'name1' AND t2.config_name = 'name2'
SET t1.config_value = 'value',
t2.config_value = 'value2';

In this query, we join two instances of the config table, labeled t1 and t2. The ON clause defines the join criteria, ensuring that the records to be updated match specific config_name values. Within the SET clause, we assign new values to the config_value columns for both t1 and t2.

Using Conditional Update

Another option is to use a conditional update, which allows you to set different values based on a specified condition.

Consider the following query:

UPDATE config
SET config_value = CASE config_name 
WHEN 'name1' THEN 'value' 
WHEN 'name2' THEN 'value2' 
ELSE config_value
END
WHERE config_name IN('name1', 'name2');

Here, we use the CASE statement to define the new value for the config_value column based on the config_name value. For names 'name1' and 'name2', the value is updated accordingly; otherwise, the current value is maintained. The WHERE clause restricts the update to records with config_name values matching the given criteria.

The above is the detailed content of How Can I Update Multiple Database Records with a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!