CODEWITHSUNDEEP
sqlsql-servert-sql
klmtsgw
klmtsgw

Reputation: 13

How to refer to the exact object

I need a script that will change status to 0 for every student that has 3 or more absences

This is what I came up with:

UPDATE Group_cast
SET gc_Status = CASE WHEN ((SELECT COUNT(at_Presence) 
    FROM Attendance INNER JOIN Student ON at_stID = st_ID 
        INNER JOIN Group_cast ON st_ID = gc_stID 
    WHERE at_Presence = 0) >= 3) THEN 0
ELSE 1 END;

but it's updating every student status to 0 on the list and not just the one with 3 or more absences.

Upvotes: 0

Views: 27

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Try this:

UPDATE Group_cast
set gc_Status =case when absentcnt>=3 then 0 end
from 
(SELECT st_ID,COUNT(at_Presence) absentcnt
    FROM Attendance INNER JOIN Student ON at_stID = st_ID         
    WHERE at_Presence = 0
    group by st_ID)t where t.st_id=Group_cast.gc_stID

Upvotes: 0

Thom A
Thom A

Reputation: 95561

The problem is your subquery has a completely separate reference to Group_cast so it's counting a grand total for every Student, not just the one relevant to the row.

With no sample data, or expected results, this is impossible to test, but perhaps this?

UPDATE GC
SET gc_Status = CASE WHEN (SELECT COUNT(A.at_Presence) --Guessed the qualifier
                           FROM dbo.Attendance A
                                JOIN dbo.Student S ON A.at_stID = S.st_ID --Guessed the qualifiers
                           WHERE S.st_ID = GC.gc_stID --Guessed the qualifiers
                             AND A.at_Presence = 0) >= 3 THEN 0 --Guessed the qualifier
                     ELSE 1
                END
FROM dbo.Group_cast GC;

Upvotes: 1

Related Questions