Microsoft SQL - Update a Table with results from a query.

i.e. update with join to a derived table.

Scenario - You have a table (TableA) where you would like to keep track of, among other statistics and information, the number of records for each Entity from another table (DataTable). This requires a method to Update one table while joined to the results of query on another table (a derived table).

The join is by EntityID.

This is how you could do it in Microsoft SQL using a T-SQL statement.

UPDATE TableA
SET TableA.RecordCount = RecCount
FROM TableA,
(SELECT EntityID, Count(EntityID) as RecCount
From DataTable Group by EntityID) as DT
where TableA.EntityID = DT.EntityID