Mysql ON DUPLICATE KEY UPDATE
I am stuck with a mySQL query using UPDATE DUPLICATE KEY UPDATE.
I am 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 a request that this is a violation:
INSERT INTO images SET picid=732, fname='hr2461809-3', path='pictures/' ON DUPLICATE KEY UPDATE picid=732, fname='hr2461809-3', path='pictures/'
I am using a very similar query elsewhere in the application without any problem. I don't know why it breaks. I was expecting that when the UNIQUE KEY on fname is violated, it will just update the row where the violation occurred?
Thanks for any help
a source to share
I think you want ON DUPLICATE KEY IGNORE
.
In the event of a key collision, you ask to simply re-insert the same data. Unsurprisingly, this leads to another key collision, since this is still a duplicate line!
ON DUPLICATE KEY IGNORE
cancels the insert if the row already exists.
a source to share