To perform an UPDATE from a SELECT in SQL Server, you typically use a subquery or a common table expression (CTE) to select the data that you want to use for the update. There are a few different approaches you can take depending on your specific requirements. Here are some examples:

Using Subquery

If you want to update a table based on values from another table, you can use a subquery in your UPDATE statement. Here’s a basic example:

UPDATE TargetTable
SET TargetTable.ColumnName = SourceTable.ColumnName
FROM TargetTable
INNER JOIN SourceTable ON TargetTable.ID = SourceTable.ID
WHERE SomeCondition = True -- Optional condition

In this example, TargetTable is the table you want to update, and SourceTable is the table containing the data you want to use for the update. You join the two tables based on a common column (ID in this case) and set the value of ColumnName in TargetTable to the value of ColumnName in SourceTable.

Using Common Table Expression (CTE)

For more complex scenarios, such as when you need to update a table based on an aggregate function or when the logic for determining the update value is complex, you can use a CTE. Here’s how you might do it:

;WITH CTE AS (
    SELECT
        ID,
        AggregateColumn = MAX(SomeColumn) -- Example of using an aggregate function
    FROM SourceTable
    GROUP BY ID
)
UPDATE TargetTable
SET TargetTable.ColumnName = CTE.AggregateColumn
FROM TargetTable
INNER JOIN CTE ON TargetTable.ID = CTE.ID

This example uses a CTE to select the maximum value of SomeColumn from SourceTable for each ID. It then updates TargetTable, setting ColumnName to the aggregated value from the CTE for each matching ID.

Update Using OUTPUT

Sometimes, you might want to capture the updated values for further processing. SQL Server’s OUTPUT clause can be used with UPDATE to achieve this:

UPDATE TargetTable
SET ColumnName = SourceTable.NewValue
OUTPUT INSERTED.ID, INSERTED.ColumnName -- Capture the updated values
FROM TargetTable
INNER JOIN SourceTable ON TargetTable.ID = SourceTable.ID

This will update TargetTable using values from SourceTable and output the updated rows’ ID and ColumnName values.

Considerations

• Ensure your JOIN condition correctly matches the rows you intend to update.
• Be cautious with your conditions to avoid unintentional updates.
• Always test your update queries on a non-production environment first to prevent data loss.

These examples cover common scenarios, but your specific case might require adjustments. Always tailor the approach to fit your data and requirements.