I have two tables

Table One

Means 2 Students Age is 5 5 Students Age is 6 and 1 Student Age is 4

Table Two

Means 2 Coaching Group is there. The first one is GroupA and the second one is GroupB GroupA age group between 3 to 5 and GroupB age group between 5 to 7 In this scenario there are 3 students in GroupA and 5 students in GroupB.

I want to update the StudentsCount column in table2 like GroupA = 3 and GroupB = 5

I am using between operator but the result is wrong.

update a
    set a.StudentsCount = b.Students 
from Table2 a, Table1 b 
where b.age between a.MinAge and a.MaxAge

When I am using the above query StudentsCount field update as 2 for both records. Already tried >= and <= but no luck. Anybody knows the simple table query please update. Thank you.

CodePudding user response：

You are using a wrong approach. There is no need for a join. You want to update table2? Then UPDATE table2. You want to update its StudentsCount? Then SET StudentsCount = .... You want the sums of the number of students? Then SUM(Students).

UPDATE table2
SET studentscount = 
(
  SELECT COALESCE(SUM(students), 0)
  FROM table1
  WHERE table1.age BETWEEN table2.minage AND table2.maxage
);

Two remarks:

• As others have mentioned, in your sample data you count students of age 5 twice, once in the range 3-5, once in the range 5-7. You probably want the ranges to be either 3-5 and 6-7 or 3-4 and 5-7 instead.
• We avoid redundancy in a database. You can always calculate the number of students ad hoc. You can even create a view for this. It is usually a bad idea to store the sums redundantly.