SQL update with SELECT results

Question

I have an extensive SQL SELECT that performs TotalNetWorth calculations for multiple users. The result is TotalNetworth and User. This may contain multiple entries. Example:

------------------------- |TotalNetWorth | UserId | ------------------------- | 24.45 | 1 | | 45.34 | 3 | ------------------------- 

What I want to do is update the NetWorth column in my Users table with TotalNetWorth and UserId = Users.Id as the key. What is the best way to do this?

Answer 1

You can use JOIN in a subquery with an alias.

 UPDATE Users FROM Users u INNER JOIN (SELECT WhatEver FROM YourQueryThatCalcsNetWorth) nw ON nw.UserID = u.UserId 

Answer 2

Something like that

 UPDATE u FROM Users u JOIN tableTotalNetWorth t ON t.UserID = u.UserId 

Answer 3

 CREATE TEMPORARY TABLE TempNetWorth AS (SELECT * FROM [your query]) UPDATE Users u, TempNetWorth t SET u.NetWorth = t.TotalNetWorth WHERE u.UserID = t.UserId 

Answer 4

make your choice first and then update it immediately with cte help

 WITH cte_query AS ( SELECT TotalNetWorth = <calculate_total> FROM [Users]) UPDATE cte_query SET TotalNetWorth = TotalNetWorth; 

Answer 5

You can do INSERT .... SELECT .... ON DUPLICATE KEY UPDATE .... as described here .

Answer 6

You may need to use your extended SELECT query to first display the data in a temporary table, say "temp", and then try to use this query:

 Update Users set NetWorth = (select TotalNetWorth from temp where Users.Id = temp.UserId)