An INSERT conditioned on COUNT

Question

How can I construct a MySQL INSERT query that only executes if the number of rows satisfying some condition already in the table is less than 20, and fails otherwise?

That is, if the table has 18 rows satisfying the condition, then the INSERT should proceed. If the table has 23 rows satisfying the condition, then the INSERT should fail.

For atomicity, I need to express this in a single query, so two requests can not INSERT at the same time, each in the 'belief' that only 19 rows satisfy the condition.

Thank you.

Answer 1

What about:

INSERT INTO TargetTable(Column1, Column2, ...)
    SELECT 'Value For Column 1', 'Value For Column 2', ...
      FROM Dual
     WHERE (SELECT COUNT(*) FROM TargetTable WHERE ...Some Condition...) < 20;

If the WHERE clause is not satisfied, no row is inserted; if the where clause is satisfied, then one row is inserted.

You can adapt the same mechanism to select from another table (instead of a single row of values from DUAL).