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
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