Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    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.

    INSERT INTO [registration]
    ( [class_id], [student_id], [student_firstname],[student_lastname], )
    VALUES (6, 'jonsmith', 'Jonathan', 'Smith')

    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
    FROM [registration]
    WHERE [class_id] = 6
    )

    Any suggestions would be greatly appreciated.
    -- grf

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following (modify based on your db structure):

    insert into registration(col1,col2) select distinct value1,value2 from registration where 24 >= (select count(*) from registration where classid = '22' )

  3. #3
    Join Date
    Sep 2002
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •