SQL Iteration Problem

Transact-SQL
1

I have a question. Suppose I have two tables. One table is details table and other one is summary table. E.g.

  1. TotalMarks.tbl, columns (Name, Surname, Total) --->Details table
  2. SubjectWiseMarks.tbl, columns (Name, Surname, Subject Name, Marks) ---> Summary table

Lets assume data in 2nd table (SubjectWiseMarks.tbl) as:
Name Surname SubjectName Marks
Sanjay Dutt English 85
Sanjay Dutt Hindi 70
Sanjay Dutt Mathematics 43
Sanjay Dutt Science 36
Sanjay Dutt Social Science 54

Now in the TotalMarks.tbl, My data would be

Name Surname TotalMarks
Sanjay Dutt 288

I am using below query to achieve this result:

update TotalMarks set Total = (select sum(Marks) from SubjectWiseMarks where name = 'Sanjay' and Surname = 'Dutt') where name = 'Sanjay' and Surname = 'Dutt';

Now my problem is to make this query iterative. I have so many students like Salman Khan, ShahRukh Khan, Ranbir Kapoor etc. I cannot keep updating for each one of them manually, one by one. I have full data in table 2 and want to update table one for each student by writing a single query, a loop of some sort. Please help me in this.

2

This will update the lot

update U
set Total = SumMarks
FROM TotalMarks AS U
    JOIN
    (
        SELECT name,
               Surname,
               sum(Marks) AS SumMarks
        FROM  SubjectWiseMarks
        GROUP BY name,
                 Surname
    ) AS T
        ON T.name = U.name
       AND T.Surname = U.Surname

TAKE A BACKUP FIRST !!

I think you should consider using a Key(s) to join [SubjectWiseMarks] and [TotalMarks] which is NOT the Name, Surname - sooner or later you will have two people with the same name.

I would have a table of StudentNames with an ID number, and then use the ID Number in other tables such as [SubjectWiseMarks] and [TotalMarks]. This also allows the Student Name to change (i.e. the ID number would not change). I don't know how often that happens, and what the age of your students are, but for example if someone got married. Or you needed to correct a typing mistake in a name ...

EDIT: Modified the aggregate column name to "SumMarks" so that it does not clash with the table name [TotalMarks]

1 Like
3

This is not working for me. It says 'SQL Command Not Properly Ended'. Plus I am confused by the above query.

I need to update TotalMarks.tbl, column -name = Total
Have you declared any variable here. Can you please rewrite the query where variable names are different than table names.

4

Try:

DECLARE  @TotalMarks table  
   ( FirstName nvarchar(30)
   , Surname nvarchar(30)
   , Total int);
DECLARE @SubjectWiseMarks table
   ( FirstName nvarchar(30)
   , Surname nvarchar(30)
   , SubjectName nvarchar(30)
   , Marks int);
INSERT @SubjectWiseMarks
        ( FirstName ,
          Surname ,
          SubjectName ,
          Marks
        )
VALUES  ( N'Sanjay'  , N'Dutt'  , N'English'       , 85 )
      , ( N'Sanjay'  , N'Dutt'  , N'Hindi'         , 70 )
      , ( N'Sanjay'  , N'Dutt'  , N'Mathematics'   , 43 )
      , ( N'Sanjay'  , N'Dutt'  , N'Science'       , 36 )
      , ( N'Sanjay'  , N'Dutt'  , N'Social Science', 54 )
      , ( N'Salman'  , N'Khan'  , N'English'       , 64 )
      , ( N'Salman'  , N'Khan'  , N'Hindi'         , 89 )
      , ( N'Salman'  , N'Khan'  , N'Mathematics'   , 75 )
      , ( N'Salman'  , N'Khan'  , N'Science'       , 62 )
      , ( N'Salman'  , N'Khan'  , N'Social Science', 92 )
      , ( N'ShahRukh', N'Khan'  , N'English'       , 87 )
      , ( N'ShahRukh', N'Khan'  , N'Hindi'         , 89 )
      , ( N'ShahRukh', N'Khan'  , N'Mathematics'   , 90 )
      , ( N'ShahRukh', N'Khan'  , N'Science'       , 91 )
      , ( N'ShahRukh', N'Khan'  , N'Social Science', 94 )
      , ( N'Ranbir'  , N'Kapoor', N'English'       , 93 )
      , ( N'Ranbir'  , N'Kapoor', N'Hindi'         , 95 )
      , ( N'Ranbir'  , N'Kapoor', N'Mathematics'   , 92 )
      , ( N'Ranbir'  , N'Kapoor', N'Science'       , 91 )
      , ( N'Ranbir'  , N'Kapoor', N'Social Science', 90 );
    
INSERT @TotalMarks
        ( FirstName, Surname, Total )
SELECT 
     FirstName
   , Surname
   , SUM(Marks)
FROM
   @SubjectWiseMarks
GROUP BY 
     FirstName
   , Surname;
SELECT 
     FirstName
   , Surname
   , Total
FROM @TotalMarks;