|
in reply to Re: MySQL and Duplicate Keys
in thread MySQL and Duplicate Keys
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 |
Re^3: MySQL and Duplicate Keys
by Coruscate (Sexton) on Jan 20, 2003 at 09:16 UTC
|
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...
| [reply] |
|
|
These were my reasons exactly. I was originally writing a method that would check for pre-existing key and then present options, etc. but was hoping to keep the process as simple as possible for the end user... You know, if they enter the same ISBN twice, it just uses the latest entered data... Transparent to the user. I came across the DUPLICATE KEY thing while browsing and thought maybe I could shoehorn it into my needs... That's why I asked for how people were using it before I showed my own or my reasons... I suppose it makes a little more sense now.
I guess I can still make it transparent to the user, it'll just require more code for the system... Not really a big deal.
I appreciate your input though,
cidaris
| [reply] |
|
