Click here to Skip to main content
14,692,034 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table in SQL which I want to update

NAME   Emp_ID   Points   TotalPoints
ABC    1        50       0
ABC    1        40       0
XYZ    2        20       0
LMN    3        30       0
LMN    3        50       0
XYZ    2        10       0
LMN    3        5        0




Please help me to update the same table as shown below by summing up the points


NAME   Emp_ID   Points   TotalPoints
ABC    1        50       90
ABC    1        40       90
XYZ    2        20       30
LMN    3        30       85
LMN    3        50       85
XYZ    2        10       30
LMN    3        5        85


What I have tried:

I am a biginer in SQL. I tried joining the same table but could find a solution. Please help
Posted
Updated 23-Apr-20 22:39pm
v2

I'd try something like that:
UPDATE dst 
  SET dst.TotalPoints= src.TotalPoints
FROM
    YourTable AS dst
    INNER JOIN
    (
        SELECT Emp_ID, SUM(Points) AS TotalPoints
        FROM YourTable
        GROUP BY Emp_ID
    ) AS src ON dst.Emp_ID = src.Emp_ID
   
v2
Comments
MadMyche 24-Apr-20 6:45am
   
+5
Maciej Los 24-Apr-20 6:59am
   
Thank you.
Try INSERT ... FROM and a JOIN to a GROUP BY:
UPDATE Points 
SET TotalPoints = tot
FROM Points p 
INNER JOIN (SELECT Emp_ID, SUM(Points) AS tot 
                   FROM Points 
                   GROUP BY Emp_id) t
ON t.Emp_ID = p.Emp_ID
   
Comments
MadMyche 24-Apr-20 6:45am
   
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900