1. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    For some reason when I am doing a REPLACE query, the record I am sending to the server just gets added to the end of the database as a new record, rather than replacing the one with the same uniqueid. I've checked over my source very carefully, and I can't see the bug - it IS getting the right uniqueID, it just refuses to REPLACE it.

    REPLACE into users (name,email,id) VALUES ('Nice Guy','nil@nil.nil',42);

    Why would this not replace record ID 42? the 'id' field is set to auto_increment primary_key.

    This is driving me insane. Any help out there?


  2. #2
  3. No Profile Picture
    rod k
    Devshed Newbie (0 - 499 posts)
    try "describe users;" and verify that your 'id' field is truly designated 'primary key'. If it is then your info IS being replaced. Let me illustrate:

    When you use "replace" it's the same as doing an "insert" followed by a "delete" of a different row with the exception that it allows (for the moment) a repeat in a unique field.
    When you do a general query (i.e. select * from table; ) you are not designating an order for the records to be returned. Usually (although there are exceptions) the records are returned in the order there were entered. Therefore, what you are seeing is the record at the end with the id '42' but if you go back and look your previous record with the id of 42 isn't there anymore between 41 and 43. (if it is then your field is not properly designated as a primary key, an index or even just as unique, because there is no way two records could contain the same info in a field that is unique).

    to make it easier to spot this try ordering your query.

    select * from users order by id;



    [This message has been edited by rod k (edited 10-05-99).]

Similar Threads

  1. Zip code validation
    By krwyth in forum MySQL Help
    Replies: 2
    Last Post: January 5th, 2004, 01:29 PM
  2. error - parent key
    By mbonfyre in forum Oracle Development
    Replies: 1
    Last Post: November 29th, 2003, 12:44 PM
  3. Accesing dates in Mysql thru PHP
    By carlmty in forum PHP Development
    Replies: 1
    Last Post: January 7th, 2003, 03:52 AM
  4. Uknow Known MYSQLERROR
    By andyw in forum MySQL Help
    Replies: 7
    Last Post: January 23rd, 2001, 09:34 AM
  5. help! insert and replace via replace
    By Alexander.Sulima in forum MySQL Help
    Replies: 0
    Last Post: March 9th, 2000, 12:14 PM

IMN logo majestic logo threadwatch logo seochat tools logo