Home >Database >Mysql Tutorial >How Can I Efficiently Update Multiple SQL Records with a Single Query?

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

Susan Sarandon
Susan SarandonOriginal
2024-12-24 10:33:13993browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn