REPLACE vs INSERT

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?

Gabe

------------------
>>>---------->

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;

HTH

Rod

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