How Can I Update a SQL Server Table Using Values from a SELECT Statement?

Updating SQL Server Tables with Data from SELECT Statements

SQL Server allows inserting rows using INSERT...SELECT. However, directly updating a table using a SELECT statement in the way one might initially attempt (e.g., UPDATE Table SET col1, col2 SELECT col1, col2...) is invalid. The correct approach involves using subqueries or joins.

Let's say you need to update a table with values from a temporary table. An incorrect attempt 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>

Correct Methods:

1. Subquery Approach:

This method uses subqueries to fetch the update values:

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

The EXISTS clause ensures updates only occur for rows with matching entries in other_table.

2. Join-Based Approach:

This approach leverages a JOIN for more efficient 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 JOIN matches rows in Table_A and Table_B based on the id column, applying updates only where Table_A.col3 equals 'cool'.

Both subqueries and joins provide effective ways to update SQL Server tables using data derived from SELECT statements, enhancing data manipulation flexibility and performance.

The above is the detailed content of How Can I Update a SQL Server Table Using Values from a SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!