If you need to update an old record in unique key, try to use REPLACE function. REPLACE could add if there has not existing record and update when it found a same unique key.
Sommai, At 19:01 5/2/2002 +0000, DL Neil wrote: >HelloErik, > > > I have a slight dilemma, and was wondering what the standard workaround > > is. I have three tables: owners (auto_increment primary key is > > "owners_id"), objects (auto_increment primary key is "objects_id"), and > > owners_objects (which is a foreign key table that I created, under > > advice from someone on this list a while back whose email address has > > changed -- there are two columns in owners_objects: "owners_id" and > > "objects_id", and there are two unique indexes on the table, > > "owners_id / objects_id" and "objects_id / owners_id" -- this is to keep > > duplicates combinations in this table, since they would only take up > > extra disk space). > > > > I am designing an application in PHP which stores the relationship > > between an Owner and an Object using the owners_objects table in a > > many-to-many relationship. When someone adds a new owner, they can > > choose from an HTML listbox any number of objects to associate with that > > owner. The PHP code creates an INSERT statement that inserts the data > > into "owners", and then takes the auto_incremented primary key of the > > last insert (which is the insert into "owners") and uses that as the > > value for the second INSERT statemetn: to insert into > > "owners_objects.owner_id". In this second INSERT statement, the > > "objects_id" of the Object(s) selected from the listbox go into the > > second column of "owners_objects". > > > > I am sure that many people have done this sort of setup. But what do > > you do to get around the problem of INSERTing a pair of values that > > already exist? Because the combinations in "owners_objects" are UNIQUE > > (the UNIQUE indexes), MySQL won't accept a pair that is already > > present. I see two possible options: > > > > 1) Check to see if the combination is already present, and if so, do not > > run the INSERT query > > 2) run the INSERT query regardless and suppress the error message > > > > The disadvantage of the first one is that it adds an extra SQL query to > > the process. The disadvantage of the second one is that I think it is > > somewhat tasteless to execute code that will knowingly error -- or > > should I just stop trying to be such a perfectionist? > > > > I would post code but this is all pseudocode right now b/c I haven't > > solved this dilemma yet -- all experimentation with this has been done > > from the mysql client. > >=option 2: whilst native-MySQL will give an errmsg in response to an >attempt to INSERT duplicates, PHP doesn't >have to pay attention! Check out MySQL_affected_rows(). > >=dn > > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
