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 <joh...@pixelated.net> wrote: From: Johnny Withers <joh...@pixelated.net> Subject: Re: SELECT and INSERT if no row is returned To: "Kyong Kim" <kykim...@gmail.com> Cc: "mysql" <mysql@lists.mysql.com> 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 <kykim...@gmail.com> 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/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=rodrigof_si...@yahoo.com
