Unanswered: conditional insert - insert where count < maxval
I am developing an online class registration system. Potential registrants can view classes and register for specific classes. Each class has a maximum number of students permitted to enroll. I'm working with an Access database. For each class registration, I create a record in a [registration] table assigning a student to a class.
I want to be able to do the INSERT conditionally. I want to be able to test the current number of entries in the [registration] table for a specific class_id and insert if below the maximum allowed. I need to do this in one sql statement to prevent concurrency problems leading to over-registration of a class.
I know this is NOT valid SQL but this might better reflect what I am trying to accomplish.
INSERT INTO [registration]
( [class_id], [student_id], [student_firstname],[student_lastname], )
VALUES (6, 'jonsmith', 'Jonathan', 'Smith')
WHERE 24 >= (
SELECT Count(class_id) AS totenrolled
WHERE [class_id] = 6
Any suggestions would be greatly appreciated.
I really appreciate the help. I finally realized that this would only work if I seeded the registration table with at least one entry. Otherwise, the SELECT DISTINCT would never return anything and the INSERT would never insert. Once a single entry was added into [registration], the INSERT worked as needed. Thanks!