How Can I Update Multiple Records in a Single SQL Query?

Multi-Record Update in SQL

Suppose you encounter a scenario where you need to update multiple records in a specific table. To accomplish this, you need to leverage the appropriate SQL syntax. In this example, we'll utilize the config table, which contains columns for config_name and config_value.

Initially, you attempted to perform the update using the following query:

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

However, this query is incorrect because it attempts to update different records using the same table alias (t1 and t2). To resolve this issue, you can utilize either multi-table update syntax or conditional update methods:

Multi-Table Update Syntax:

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';

This query joins the config table twice, using aliases t1 and t2, and then uses the ON clause to specify the join condition. The SET clause then assigns the new values to the appropriate columns.

Conditional Update:

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');

This query uses the CASE statement to specify different values for different config_name values. The WHERE clause restricts the update to only affect records where config_name is either 'name1' or 'name2'.

These methods provide effective ways to update multiple records in a single query. By utilizing the appropriate syntax, you can efficiently modify the data in your SQL database.

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