MySQL Insert Unique and Only If Condition Is True

Question

I have a table named batch1 with just one column in it id which is primary key.

I can insert data into it with following query.

INSERT INTO batch1 VALUES(NULL);

Now I need to set limit on records for this table so it may only insert required number of rows. For example say if limit is set 1000, the above table must not insert more records above that and give error.

I am storing limit in other table. What are my option other than LOCKS?

Write a trigger? dev.mysql.com/doc/refman/5.0/en/create-trigger.html — MatBailie
– MatBailie, Commented Aug 14, 2012 at 16:55

eswald · Accepted Answer · 2012-08-14 19:02:09Z

2

Building on Andomar's answer, which throws a syntax error for my version of MySQL:

insert into batch1 (id) select null from ( select count(*) as num from batch1 ) as counted where counted.num < 1000;

This selects from a derived table, using the result to return either one or zero rows from the outer select.

answered Aug 14, 2012 at 19:02

eswald

8,4145 gold badges31 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eswald Over a year ago

MySQL's primary key handling enforces uniqueness. This query simply inserts NULL as the id, just like your example query does. For that to work at all (more than once, anyway), the column needs to be set as AUTO_INCREMENT. There are situations where an AUTO_INCREMENT column can re-use values previously deleted from the table, but it will never insert a value that currently exists.

Umair A. Over a year ago

Got it. What if I want to get the last inserted id? I can use LAST_INSERT_ID() but that also returns when the INSERT didn't work.

eswald Over a year ago

You could check both last_insert_id() and row_count(). In one query: select id from (select last_insert_id() as id) as subq where row_count() > 0;

Umair A. Over a year ago

Because I was executing dynamic query, row_count was always 0. Fixed it and it's working. I am not sure why we need to deallocate variables in MySQL.

Andomar · Accepted Answer · 2012-08-14 16:59:02Z

You could use a where clause to prevent unwanted updates:

insert into batch1 select null where ( select count(*) from batch1 ) < 1000

You can test with row_count whether the update got through:

select row_count() as InsertedRecords;

Collectives™ on Stack Overflow

MySQL Insert Unique and Only If Condition Is True

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related