Update particular field of a table from values of Another Table

Question

I have two tables : The first table is this :-

From this table I want to SUM only week3 info i.e the pointsRewarded and Hours field into the below table's TotalPoints and TotalHours field.

i already have the tables created.I just want to update the below table.

Please help me with the query or give me some suggestions on how to solve this.

Thanks a lot.

duplicate, see http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — swe, Sep 12 '16 at 11:31
Please always remember to Google first before asking a question on Stack Overflow. A query for `update table from other table` sqlserver will turn up dozens of answers to your question. — swe, Sep 12 '16 at 11:54

score 0 · Answer 1 · answered Sep 11 '16 at 17:36

INSERT INTO [dbo].[Table2] (
    [EmployeeID]
    ,[Employee Name]
    ,[TotalPoints]
    ,[TotalHours]
    )
SELECT  
    [EmployeeID]
    ,[Employee Name]
    ,SUM(pointRewarded) as [TotalPoints]
    ,SUM([Hours]) as [TotalHours]
FROM [dbo].[Table1]
WHERE WeekNumber='week3'
GROUP BY   
    [EmployeeID]
    ,[Employee Name]

GO

score 0 · Accepted Answer · answered Sep 11 '16 at 18:03

I am going to guess that the first table already exists and you want to add in the week 3 values:

update t2
    set pointsrewarded = t1.pointsrewarded + t2.pointsrewarded,
        hours = t1.hours + t2.hours
    from t2 join
         (select t1.employeeid, sum(pointsrewarded) as pointsrewarded
                 sum(hours) as hours, 
          from t1
          where t1.weeknumber = 'week3'
          group by t1.employeeid
         ) t1
         on t2.employeeid = t1.employeeid;

score 0 · Answer 3 · answered Sep 11 '16 at 18:28

You can use cte in this situation .

;WITH cte_1
         as (SELECT employeeid
                   ,SUM(pointsrewarded) as Week3TotalPoints
                   ,SUM([hours]) as Week3TotalHours
              FROM YourSourceTable  
              WHERE WeekNumber = 'week3'
              GROUP BY employeeid)
        UPDATE d
        SET d.TotalPoints=d.TotalPoints+c.Week3TotalPoints,
            d.TotalHours=d.TotalHours+c.Week3TotalHours
        FROM cte_1 c
         JOIN YourDestinationTable d on c.employeeid=d.employeeid

score 0 · Answer 4 · answered Sep 12 '16 at 11:25

Try this

UPDATE table2 
SET    totalpoints = totalpoints+(SELECT Sum(E.pointrewarded) 
                      FROM   table1 E 
                      WHERE  E.weeknumber = 'week3' 
                             AND E.employeeid = employeeid), 
       totalhours = totalhours+(SELECT Sum(E.hours) 
                     FROM   table1 E 
                     WHERE  E.weeknumber = 'week3' 
                            AND E.employeeid = employeeid)

Update particular field of a table from values of Another Table

4 Answers4