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

SQL - Updating Multiple Records with a Single Query

In the realm of SQL, it is often necessary to update multiple records in a database table. While straightforward, executing separate UPDATE statements for each record can be tedious and inefficient. This article explains how to update multiple records in a single query using two alternative syntaxes.

Multi-Table Update Syntax

The multi-table update syntax allows you to simultaneously update records from multiple tables that are joined by their common columns. For example, if you have a config table with columns config_name and config_value, you can update the values for multiple configurations using the following query:

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

Conditional Update Syntax

Another approach is to use the conditional update syntax, which updates selected rows based on their values. The following query updates the config_value for specific configurations using a CASE statement:

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

Conclusion

These techniques provide efficient methods to update multiple records in a single query, streamlining your database operations and enhancing your SQL programming capabilities.

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