How Can I Update SQL Server Records Using Data from Another Table via a SELECT Statement?

Leveraging SELECT Statements for SQL Server Record Updates

SQL Server offers the INSERT...SELECT statement for inserting rows. This guide demonstrates how to similarly update existing records using data sourced from another table.

Update Query Structure

The following syntax facilitates updating records based on data from a SELECT statement:

UPDATE Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Table_A AS Table_A
    INNER JOIN Table_B AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool';

Detailed Explanation

• UPDATE Table_A: Specifies the target table for updates.
• SET: Assigns values from Table_B to specified columns in Table_A.
• FROM: Identifies the tables involved in the update operation.
• INNER JOIN: Links Table_A and Table_B based on a common column.
• ON: Defines the join condition (matching columns).
• WHERE: Filters the rows updated based on a specific condition.

Practical Example

Imagine Table_A with columns id, col1, col2, and col3, and Table_B (potentially a temporary table) containing updated values for id, col1, and col2.

To update Table_A using data from Table_B where col3 is 'cool', use this query:

UPDATE Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Table_A
    INNER JOIN Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool';

This query joins the tables on the id column, updating col1 and col2 in Table_A only for rows where col3 equals 'cool' in both tables. This ensures that only matching records are updated.

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