views:

95

answers:

1

Hi-- I'm stuck on a mySQL query using ON DUPLICATE KEY UPDATE.

I'm getting the error:

mySQL Error: 1062 - Duplicate entry 'hr2461809-3' for key 'fname'

The table looks like this:

id int(10) NOT NULL default '0',   
picid int(10) unsigned NOT NULL default '0',
fname varchar(255) NOT NULL default '',  
type varchar(5) NOT NULL default '.jpg',  
path varchar(255) NOT NULL default '',    
PRIMARY KEY  (id),  
UNIQUE KEY fname (fname),  
KEY picid (propid)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  

And the query that's breaking is this:

INSERT INTO images SET picid=732, fname='hr2461809-3', path='pictures/' ON DUPLICATE KEY UPDATE picid=732,  fname='hr2461809-3', path='pictures/' 

I'm using a very similar query elsewhere in the app with no issues. I'm not sure why this one breaks. I expected that when the UNIQUE KEY on fname gets violated, that it would simply update the row where the violation occurred?

Thanks for any help

+1  A: 

I think you want ON DUPLICATE KEY IGNORE.

You're asking, in the event of a key collision, to simply re-insert the same data. Unsurprisingly this results in another key collision, as it's still a duplicate row!

ON DUPLICATE KEY IGNORE will abort the insert if the row already exists.

rjh
To further explain, ON DUPLICATE KEY UPDATE simply changes what information is being inserted with the query, so if you make it the same it will still error out. That function is there so you can make changes to it in the same query so it won't error out.
animuson
Thanks guys! I appreciate the information.
julio