List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:August 18 2005 5:53am
Subject:Re: INSERT record IF NOT EXISTS?
View as plain text  
At 7:10 PM -0700 8/17/05, Daevid Vincent wrote:
>Does mySQL have a way to INSERT a new record if one doesn't exist (based
>upon primary compound key)?
>
>I see this "EXISTS" but not an example of how to use it with INSERT.
>
>I see "INSERT... ON DUPLICATE KEY UPDATE col_name=expr" which is very close,
>but I want it to do nothing on duplicate key. :(


Perhaps you could update using the same value? Eg:

	INSERT ... ON DUPLICATE KEY UPDATE col_name=col_name

If you're worried about side-effects (eg; updating a timestamp column 
incorrectly), I believe that MySQL will not perform the update if the 
column value does not change.

	steve


>mysqladmin  Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i386
>
>CREATE TABLE `release_test` (                            
>   `BID` int(10) unsigned NOT NULL default '0',           
>   `ReleaseID` smallint(5) unsigned NOT NULL default '0', 
>   `Tested` tinyint(1) unsigned NOT NULL default '0',     
>   `CoreID` smallint(3) unsigned NOT NULL default '0',
>   KEY `BID` (`BID`,`ReleaseID`),                         
>   KEY `ReleaseID` (`ReleaseID`)                          
>) TYPE=MyISAM;
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
Thread
INSERT record IF NOT EXISTS?Daevid Vincent18 Aug
Re: INSERT record IF NOT EXISTS?Jasper Bryant-Greene18 Aug
Re: INSERT record IF NOT EXISTS?Paul DuBois18 Aug
Re: INSERT record IF NOT EXISTS?Steve Edberg18 Aug