Can I Use SELECT Statements to Update Tables in SQL Server?

Mastering Table Updates with SELECT Statements in SQL Server

SQL Server's INSERT...SELECT statement simplifies adding rows using data from another table. But can you use a similar approach for updating tables? This guide explains how.

Let's say you have a temporary table with the updated values you want to merge into your main table. A common (but incorrect) approach might look like this:

<code class="language-sql">UPDATE Table SET col1, col2
SELECT col1, col2 
FROM other_table 
WHERE sql = 'cool'
WHERE Table.id = other_table.id</code>

This syntax is flawed. The correct method leverages joins for efficient and accurate updates:

<code class="language-sql">UPDATE Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
    ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'</code>

This refined query uses a JOIN to link rows from Some_Table (aliased as Table_A) and Other_Table (aliased as Table_B) based on matching id values. The WHERE clause filters the update to only those rows where Table_A.col3 = 'cool'. This ensures data integrity and precision when updating your tables using data retrieved via a SELECT statement.

The above is the detailed content of Can I Use SELECT Statements to Update Tables in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!