Update a table using JOIN in SQL Server?
Update a table using JOIN in SQL Server?
Update a table using JOIN in SQL Server?
Re: Update a table using JOIN in SQL Server?
You don't quite have SQL Server's proprietary `UPDATE FROM` syntax down. Also not sure why you needed to join on the `CommonField` and also filter on it afterward. Try this:
```
UPDATE t1
SET t1.CalculatedColumn = t2.[Calculated Column]
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.CommonField = t2.[Common Field]
WHERE t1.BatchNo = '110';
```
If you're doing something silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see [here](http://technet.microsoft.com/en-us/library/ms190766.aspx) and [here](https://sqlblog.org/2016/01/06/s1-backtobasics-ctes) for more details:
```
;WITH t2 AS
(
SELECT [key], CalculatedColumn = SUM(some_column)
FROM dbo.table2
GROUP BY [key]
)
UPDATE t1
SET t1.CalculatedColumn = t2.CalculatedColumn
FROM dbo.table1 AS t1
INNER JOIN t2
ON t1.[key] = t2.[key];
```
The reason this is silly, is that you're going to have to re-run this entire update every single time any row in `table2` changes. A `SUM` is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.
```
UPDATE t1
SET t1.CalculatedColumn = t2.[Calculated Column]
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.CommonField = t2.[Common Field]
WHERE t1.BatchNo = '110';
```
If you're doing something silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see [here](http://technet.microsoft.com/en-us/library/ms190766.aspx) and [here](https://sqlblog.org/2016/01/06/s1-backtobasics-ctes) for more details:
```
;WITH t2 AS
(
SELECT [key], CalculatedColumn = SUM(some_column)
FROM dbo.table2
GROUP BY [key]
)
UPDATE t1
SET t1.CalculatedColumn = t2.CalculatedColumn
FROM dbo.table1 AS t1
INNER JOIN t2
ON t1.[key] = t2.[key];
```
The reason this is silly, is that you're going to have to re-run this entire update every single time any row in `table2` changes. A `SUM` is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.