Set column value with subquery

Question

UPDATE Users
SET GeneralPoint =(SELECT PostResult.AveragePoint  FROM Users 
LEFT JOIN (SELECT AVG(PostsLikes.PostPoint) AS AveragePoint , PostsLikes.ID_User 
            FROM PostsLikes,Users
            WHERE PostsLikes.ID_User = Users.Id
            GROUP BY PostsLikes.ID_User) PostResult
ON Users.Id = PostResult.ID_User)

This subquery return four element i want to write each of them to 'GeneralPoint' of every user. — samir_42, Mar 18 '19 at 17:17
So do you want all User records to have one specific value or are you trying to do a bulk `Update` with a `Select`? — Ryan Wilson, Mar 18 '19 at 17:18
Yes. I have 10 User in Users table. But i want to write to user's general point for only 4 user. Because this subquery return 4 value. — samir_42, Mar 18 '19 at 17:20

score 0 · Answer 1 · answered Mar 18 '19 at 17:19

0

You can use correlated subquery :

UPDATE u
     SET u.GeneralPoint = (SELECT AVG(pl.PostPoint) FROM PostsLikes pl WHERE pl.ID_User = u.id )
FROM Users u;

answered Mar 18 '19 at 17:19

Yogesh Sharma

49,870
5
26
52

I wrote like it. But this subquery return more value – samir_42 Mar 18 '19 at 17:21
@SamirH-evHemzeyev . . . Then you did not write it like this. The correlated subquery returns exactly one row with one column. – Gordon Linoff Mar 18 '19 at 18:23

Ryan Wilson · Answer 2 · 2019-03-18T17:28:42.117

--This should update all the user columns with the subquery values
--This works as it joins the Users table on your subquery by Users.Id
--I added the Users.Id value to the initial sub-query so you can join on it for the update
UPDATE Users
SET GeneralPoint = x.AveragePoint
FROM Users AS u
INNER JOIN
(SELECT PostResult.AveragePoint, Users.Id  FROM Users 
LEFT JOIN (SELECT AVG(PostsLikes.PostPoint) AS AveragePoint , PostsLikes.ID_User 
            FROM PostsLikes,Users
            WHERE PostsLikes.ID_User = Users.Id
            GROUP BY PostsLikes.ID_User) PostResult
ON Users.Id = PostResult.ID_User) AS x
ON x.Id = u.Id

Reference to Update with a Select statement: (How do I UPDATE from a SELECT in SQL Server?)

score 0 · Accepted Answer · answered Mar 18 '19 at 17:29

0

use join

 UPDATE u
 set  GeneralPoint =b.avg_val
 from  Users u join

(SELECT AVG(pl.PostPoint) avg_val,pl.ID_User
 FROM PostsLikes pl group by pl.ID_User
 ) b on u.Id=b.ID_User

answered Mar 18 '19 at 17:29

Zaynul Abadin Tuhin

31,407
5
33
63

Set column value with subquery

3 Answers3