CODEWITHSUNDEEP
sqlsql-serverselectsql-update
user3332742
user3332742

Reputation: 33

update existing column with results of select query using sql

I am trying to update a column called Number_Of_Marks in our Results table using the results we get from our SELECT statement. Our select statement is used to count the numbers of marks per module in our results table. The SELECT statement works and the output is correct, which is

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

The table in use is:

Results: ResultID, ModuleID, Number_Of_Marks

We need the results of cnt to be updated into 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)

Upvotes: 3

Views: 7287

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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

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 the subquery, not from the uninitialized variable.

EDIT:

In general, when you change the question it is better to ask another question. Sometimes, though, the changes are really small. The revised query looks something like:

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;

Note that I multiplied the marks by 1.0. This is a quick-and-dirty way to convert an integer to a numeric value. SQL Server takes averages on integers and produces an integer. Usually you want some sort of decimal or floating value.

Upvotes: 4

Related Questions