Re: SELECT and INSERT if no row is returned
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=arch...@jab.org
Re: SELECT and INSERT if no row is returned
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
Re: Birthday Calendar
On Tue, Mar 23, 2010 at 11:07 PM, Noel Butler noel.but...@ausics.netwrote: does this list not have a dickhead filter? That's Eternal September for you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT and INSERT if no row is returned
I needed to give greater detail. parent_id isn't unique. The table has a composite primary key (parent_id, seq_id). Here's a better schema def CREATE TABLE sometable ( parent_id INT(10) NOT NULL, seq_id INT(10) AUTO_INCREMENT, child_id INT(10) NULL, PRIMARY KEY(parent_id, seq_id), UNIQUE KEY(child_id) ) ENGINE=INNODB; The requirement is that there can be only 1 parent_id associated with a given child or there can be only one parent_id not associated with a child_id (NULL child_id). I need to avoid a race condition where 2 connections can SELECT and return an empty row and insert rows of the same parent_id not associated with a message_id. It's that .1% of the cases we want to avoid. Kyong On Wed, Mar 24, 2010 at 6:26 AM, Rodrigo Ferreira rodrigof_si...@yahoo.comwrote: 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
Re: SELECT and INSERT if no row is returned
Kyong Kim wrote: I needed to give greater detail. parent_id isn't unique. The table has a composite primary key (parent_id, seq_id). Here's a better schema def CREATE TABLE sometable ( parent_id INT(10) NOT NULL, seq_id INT(10) AUTO_INCREMENT, child_id INT(10) NULL, PRIMARY KEY(parent_id, seq_id), UNIQUE KEY(child_id) ) ENGINE=INNODB; The requirement is that there can be only 1 parent_id associated with a given child or there can be only one parent_id not associated with a child_id (NULL child_id). I need to avoid a race condition where 2 connections can SELECT and return an empty row and insert rows of the same parent_id not associated with a message_id. It's that .1% of the cases we want to avoid. What you are describing is a UNIQUE key based on the combination of parent_id and child_id. ALTER TABLE sometable ADD UNIQUE(parent_id, child_id); Based on your descriptions, that should fix your duplication problems. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
