http://qs321.pair.com?node_id=228270

cidaris has asked for the wisdom of the Perl Monks concerning the following question:

Does anyone have experience with using "ON DUPLICATE KEY UPDATE" with DBI and MySQL? I've been having a number of problems, but was hoping maybe someone could post a brief snippet of how they do it before I go into depth. As usual, I'd rather do it the hard way than have someone fix it for me ;)

Thanks in advance.
cidaris

Replies are listed 'Best First'.
Re: MySQL and Duplicate Keys
by Coruscate (Sexton) on Jan 20, 2003 at 03:34 UTC

    http://www.mysql.com/doc/en/INSERT.html tells you the use for ON DUPLICATE KEY UPDATE, so you may want to check that out.

    I'd be interested to hear more details about the situation which you are stuck in, as I'd like to see an example of where you'd want to use this feature. Myself, I use primary keys because of the fact that they normally must be unique. I have yet to come across a case where refusing to add data to the table because of an existing primary key value is a bad idea.

    I hope you post more info so that I can understand your position. ON DUPLICATE KEY UPDATE acts differently depending on how the table is set up (ie: if more than one column is marked as unique). The link at the beginning of the post vaguely mentions this fact.

      Well, perhaps a bit of reasoning. It may clear up some things.
      First off, let's make an assumption. Every book published in the US has an ISBN number which is guaranteed to be unique. I'd use that for my primary key.
      A theoretical fill-in form could ask for ISBN number, a title, a price, maybe an author, etc. I figured I could use an INSERT statement to allow them to input their own data. In the event that a book had already been entered with an ISBN, it would simply update that old record, as is documented.
      I figured I could have MySQL do the work for me by using INSERT with ON DUPLICATE KEY instead of doing a bunch of checking to see if that book was already entered, etc. and doing an UPDATE/SET on the record where that key was. Basically, I just thought I'd found a way to be lazy.
      The "ISBN" is the only key, and the only unique column.

      I guess my syntax would be
      my $sth = $dbh->prepare("INSERT INTO listings (isbn,price,author) VALUES ($isbn,$price,$author) ON DUPLICATE KEY UPDATE [whichever values, yada yada]
      Perhaps this is not an advisable way of doing this?
      Thanks,
      cidaris

        I don't really know much of anything about ON DUPLICATE KEY UPDATE, but I don't think it does what you are hoping for. Even with the duplicate key update in there, your primary key values still have to be unique. What I believe that directive does is allow you to change the existing primary key value to make room for the one you're trying to insert. In your case, you can't change the ISBN number, so you'll either have to check to see if the ISBN number is already entered, or you could simply pass the IGNORE directive with the INSERT. If the ISBN is already entered, then no error will be generated, but the table will not be updated in any way.

        Your best bet would be to check the existence of the ISBN and then present the user entering the data with options for selecting which title/author, etc fits the bill the best. So if 2 people issue the same ISBN, but they supply slightly different titles, ask the user to select between their option or the option already in the DB. I'm not sure how well that system would work, but I'm sure you'll come up with something.

        One more possible method perhaps: have you thought about supplying the ISBN number to an online book store and retrieving book info from the html? Just an idea...