Geek asks and answers

Refresh existing column with select query results using sql

I am trying to update a column with a name Number_Of_Marksin our table Resultsusing the results obtained from our statement SELECT. Our select statement is used to count the number of labels per module in our result table. The operator SELECTworks, and the conclusion is correct, that

 ResultID   ModuleID   cnt
 -------------------------
   111      ART3452    2                                                          
   114      ART3452    2                                                         
   115      CSC3039    3                                                        
   112      CSC3039    3                                                        
   113      CSC3039    3

Used table:

Results: ResultID, ModuleID, Number_Of_Marks

We need the cnt results to be updated in our Number_Of_Marks column. This is our code below ...

DECLARE @cnt INT

SELECT @cnt

SELECT C.cnt
  FROM Results S
       INNER JOIN (SELECT ModuleID, count(ModuleID) as cnt
                     FROM Results 
                    GROUP BY ModuleID) C ON S.ModuleID = C.ModuleID

    UPDATE Results 
    SET [Number_Of_Marks] = (@cnt)
+3
source share
1 answer

You can do this in SQL Server using the update/ syntax join:

UPDATE s
  SET [Number_Of_Marks] = c.cnt
  FROM Results S INNER JOIN
       (SELECT ModuleID, count(ModuleID) as cnt
        FROM Results 
        GROUP BY ModuleID
       ) C
       ON S.ModuleID = C.ModuleID;

I assume that you want the count from a subquery, not from an uninitialized variable.

EDIT:

, , . , , . :

UPDATE s
  SET [Number_Of_Marks] = c.cnt,
      Marks = avgmarks
  FROM Results S INNER JOIN
       (SELECT ModuleID, count(ModuleID) as cnt, avg(marks * 1.0) as avgmarks
        FROM Results 
        GROUP BY ModuleID
       ) C
       ON S.ModuleID = C.ModuleID;

, marks 1.0. . SQL Server . - .

+4

More articles:


All Articles