strange behavior of REPLACE

wxdqz

New Member
I have a temporary table that I'm filling with results of several searches. When two or more searches return the same record, I'd like to have them overwrite the identical record. From what I read in the manual, I should be able to do that with a REPLACE...SELECT query. However, when I test such a query (on the mysql command line), it just appends identical records:

mysql> REPLACE srch_tmp SELECT * FROM individuals WHERE state REGEXP "PA";
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT first, last FROM srch_tmp WHERE state REGEXP "PA";
+-----------+---------+
| first | last |
+-----------+---------+
| Daniel K. | Richter |
| Carol | Kammen |
+-----------+---------+
2 rows in set (0.01 sec)

mysql> REPLACE srch_tmp SELECT * FROM individuals WHERE state REGEXP "PA";
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT first, last FROM srch_tmp WHERE state REGEXP "PA";
+-----------+---------+
| first | last |
+-----------+---------+
| Daniel K. | Richter |
| Carol | Kammen |
| Daniel K. | Richter |
| Carol | Kammen |
+-----------+---------+
4 rows in set (0.00 sec)

Does anyone know why REPLACE would act this way?

Thanks in advance,

Reha
 
Back
Top