update existing column with results of select query using sql

select, sql, sql-server, sql-update

Solution

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.

Problem

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) ```

Original source