1

Consider my select statement:

    SELECT course_id from courses 
    WHERE
    course_id = (
      SELECT course_id FROM courses
      WHERE  course_id = 3
      AND max_size > students_registered);
  • The values that are to be inserted are not coming from the select. They are submitted from a form by the user. The select tells me if there are slot available to make the insert.

Currently I do this check on the front end with 1 Ajax call, I'm trying to move this away from the front end. Any suggestions would be greatly appreciated.

  • The insert statement only inserts 8 values into a table. Nothing extraordinary that would value to this question. I've researched extensively. Is my only option moving everything into a stored procedure?

  • I need this insert to fail when the select returns no rows or null so I can return the right view/jsp to my application.

Please let me know any details I could provide to help.

Perdomoff
  • 938
  • 2
  • 7
  • 26
  • use a before trigger on insert – davejal Feb 02 '16 at 23:36
  • Possible duplicate of [MySQL Trigger to prevent INSERT under certain conditions](http://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions) – davejal Feb 02 '16 at 23:42
  • @davejal the before trigger is the way I went although the SO question you mention doesn't mention the signal code 4500 that does the actual fail. Would you say it's still a duplicate? – Perdomoff Feb 04 '16 at 05:13
  • 1
    It all depends on the interpretation, but you found a resolution there, so we don't need to discus if it is a duplicate or not. – davejal Feb 04 '16 at 16:59
  • As I don't like your reputation right now (666) I upvoted this question. – davejal Feb 04 '16 at 17:00
  • 1
    @davejal, Your suggestion was right on target. And also Ive been thinking about that 666 same thing since yesterday, ty. – Perdomoff Feb 04 '16 at 17:01

2 Answers2

2

You can use INSERT ... SELECT judiciously to control this.

For example,

INSERT INTO mytable (course_id, col2, col3)
SELECT a.course_id AS col1, 'value' AS col2, 'another_value' AS col3
  FROM courses a
 WHERE course_id = 3
   AND max_size > students_registered
 LIMIT 1

The trick is to write a SELECT query that returns exactly the values you want to insert, and returns either one row or none depending on your conditions. Write that query and debug it. Make sure it works.

Then put the INSERT clause right before it.

If the SELECT comes up with no rows, the INSERT will insert nothing. If the SELECT comes up with one row, that's what will be inserted.

This is a great way to do this; you don't have to worry about race conditions or transactions.

Edit. Notice that the SELECT statement contains a mixture of values derived from the table and constant values. If you don't use any values from the table, it could look like this:

 SELECT ?param1 AS col1, ?param2 AS col2, ?param3 AS col3

You did't mention your host language, so the ?param items above are just my guess at appropriate placeholders for data coming from a form.

This form of the SELECT still does what you want; if the WHERE clause returns no rows you get nothing inserted.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
1

One method would be to incorporate this into the insert statement. I think this would be the logic:

insert into courses (course_id, . . . )
    select 3, . . .
    from dual
    where exists (select 1 from courses c where course_id = 3 and max_size < students_registered);

You can also write a trigger to incorporate similar logic. Other databases implement check constraints, but alas, MySQL does not actually do anything with them.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786