First, if you want no duplicate parent_id, make it unique key (as JW saids). Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve the problem with one statement.
Rodrigo Ferreira --- On Wed, 3/24/10, Johnny Withers <[email protected]> wrote: From: Johnny Withers <[email protected]> Subject: Re: SELECT and INSERT if no row is returned To: "Kyong Kim" <[email protected]> Cc: "mysql" <[email protected]> Date: Wednesday, March 24, 2010, 9:32 AM Make parent_id a unique key. Doing a select first and inserting if no result will work 99.9% of the time; however, consider 2 rows being inserted at the same time. JW On Tuesday, March 23, 2010, Kyong Kim <[email protected]> wrote: > I need to INSERT a row into a table only if it does not exist to > insure that there won't be duplicate rows. > Due to the the usage case and the design of the primary key, > non-unique id + auto increment, I don't think insert ignore is an > option. > > What would be simplest and cheapest way to make sure that given the > following schema > > create table some_table > ( parent_id int //non-unique > seq_id int auto_increment ) ENGINE=INNODB > > that no row with the same parent_id can be inserted? > > Kyong > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[email protected] > > -- ----------------------------- Johnny Withers 601.209.4985 [email protected] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[email protected]
