Error - An aggregate may not appear in the set list of an UPDATE state

Hi Tuhi

  In Design mode where i can write this code in column. I want Attendance column should get automatically updated

UPDATE [SessionDeliveryParticipant]
SET Attendance = CASE
    WHEN (
        SELECT COUNT(*)
        FROM [SessionDeliveryParticipant] AS sub
        WHERE sub.SessionDeliveryCalendarID = [SessionDeliveryParticipant].SessionDeliveryCalendarID
        AND sub.PresentInSession = 0
    ) = (
        SELECT COUNT(*)
        FROM [SessionDeliveryParticipant] AS sub2
        WHERE sub2.SessionDeliveryCalendarID = [SessionDeliveryParticipant].SessionDeliveryCalendarID
    ) THEN 1
    ELSE 0
END;

Thanks

UPDATE [SessionDeliveryParticipant]
SET Attendance = CASE
    WHEN (
        SELECT COUNT(*)
        FROM [SessionDeliveryParticipant] AS sub
        WHERE sub.SessionDeliveryCalendarID = [SessionDeliveryParticipant].SessionDeliveryCalendarID
        AND sub.PresentInSession = 0
    ) = (
        SELECT COUNT(*)
        FROM [SessionDeliveryParticipant] AS sub2
        WHERE sub2.SessionDeliveryCalendarID = [SessionDeliveryParticipant].SessionDeliveryCalendarID
    ) THEN 1
    ELSE 0
END;

In this modified code, I added [SessionDeliveryParticipant]. as a prefix to the SessionDeliveryCalendarID column to clarify the table reference. Check that the column is referenced correctly in both the inner and outer queries. Please note that the code assumes you have a table named SessionDeliveryParticipant with columns named SessionDeliveryCalendarID, Attendance, and PresentInSession.