Update column with average values in same table using JOIN - SQL Server
<p>I have following table </p>

<pre><code>[Table_01]
ID  |   Name    |   Profit  |   AvgProfit
------------------------------------------
1   |   Donald  |   1001    |   
2   |   Hillary |   101     |   
3   |   Barack  |   11      |   
4   |   Bernie  |   1       |   
1   |   Donald  |   1002    |   
2   |   Hillary |   102     |   
3   |   Barack  |   12      |   
4   |   Bernie  |   2       |   
1   |   Donald  |   1003    |   
2   |   Hillary |   103     |   
3   |   Barack  |   13      |   
4   |   Bernie  |   3       |   
</code></pre>

<p>I need to add average Profit value for each person. </p>

<p>Result after UPDATE should be: </p>

<pre><code>[Table_01]
ID  |   Name    |   Profit  |   AvgProfit
------------------------------------------
1   |   Donald  |   1001    |   1002
2   |   Hillary |   101     |   102
3   |   Barack  |   11      |   12
4   |   Bernie  |   1       |   1
1   |   Donald  |   1002    |   1002
2   |   Hillary |   102     |   102
3   |   Barack  |   12      |   12
4   |   Bernie  |   2       |   1
1   |   Donald  |   1003    |   1002
2   |   Hillary |   103     |   102
3   |   Barack  |   13      |   12
4   |   Bernie  |   3       |   1
</code></pre>

<p>I do get average values using: </p>

<pre><code>SELECT z1.*, AVG(Profit) OVER(PARTITION BY Name) AVERAGE
    FROM Table_01 z1
    order by Name
</code></pre>

<p>For update I've tried multiple JOIN clauses with no success.
I get various errors: using AGGREGATE in JOIN and invalid object names. </p>

<p>This one works in Oracle, i suppose. </p>

<pre><code>UPDATE Table_01 z1 
SET z1.AvgSecs = (SELECT AVG(Profit) 
                 FROM Table_01
                 WHERE Name = z1.Name)
</code></pre>

<p>What is the syntax used in Microsoft SQL Server 2014?</p>

<h2>QUESTION 2</h2>

<p>I have another table which i need to update</p>

<pre><code>[Table_02]
Name    |   AvgProfit                   Name    |   AvgProfit
---------------------   UPDATE --&gt;      ---------------------
Donald  |                               Donald  |   1002
Hillary |                               Hillary |   102
Barack  |                               Barack  |   12
Bernie  |                               Bernie  |   1
</code></pre>

<p>I wonder if this works: </p>

<pre><code>UPDATE t2
SET t2.AvgProfit = t1.AvgProfit
FROM Table_02 t2
    join Table_01 t1 
    ON   t1.Name = t2.Name 
</code></pre>

<p>"Name" is a Unique value</p>

<p>Great thanks for all.</p>