Does replace into have a where clause?

Question

I'm writing an application and I'm using MySQL as DBMS, we are downloading property offers and there were some performance issues. The old architecture looked like this: A property is updated. If the number of affected rows is not 1, then the update is not considered successful, elseway the update query solves our problem. If the update was not successful, and the number of affected rows is more than 1, we have duplicates and we delete all of them. After we deleted duplicates if needed if the update was not successful, an insert happens. This architecture was working well, but there were some speed issues, because properties are deleted if they were not updated for 15 days. Theoretically the main problem is deleting properties, because some properties are alive for months and the indexes are very far from each other (we are talking about 500, 000+ properties).

Our host told me to use replace into instead of deleting properties and all deprecated properties should be considered as DEAD. I've done this, but problems started to occur because of syntax error and I couldn't find anywhere an example of replace into with a where clause (I'd like to replace a DEAD property with the new property instead of deleting the old property and insert a new to assure optimization). My query looked like this:

replace into table_name(column1, ..., columnn) values(value1, ..., valuen) where ID = idValue

Of course, I've calculated idValue and handled everything but I had a syntax error. I would like to know if I'm wrong and there is a where clause for replace into.

I've found an alternative solution, which is even better than replace into (using simply an update query) because deletes are happening behind the curtains if I use replace into, but I would like to know if I'm wrong when I say that replace into doesn't have a where clause. For more reference, see this link:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Thank you for your answers in advance, Lajos Árpád

Answer 1

In your documentation link, they show three alternate forms of the replace command. Even though elided, the only one that can accept a where clause is the third form with the trailing select.

replace seems like overkill relative to update if I am understanding your task properly.

Answer 2

I can see that you have solved your problem, but to answer your original question:

REPLACE INTO does not have a WHERE clause.

The REPLACE INTO syntax works exactly like INSERT INTO except that any old rows with the same primary or unique key is automaticly deleted before the new row is inserted.

This means that instead of a WHERE clause, you should add the primary key to the values beeing replaced to limit your update.

REPLACE INTO myTable (
  myPrimaryKey,
  myColumn1,
  myColumn2
) VALUES (
  100,
  'value1',
  'value2'
);

...will provide the same result as...

UPDATE myTable
SET myColumn1 = 'value1', myColumn2 = 'value2'
WHERE myPrimaryKey = 100;

...or more exactly:

DELETE FROM myTable WHERE myPrimaryKey = 100;
INSERT INTO myTable(
  myPrimaryKey,
  myColumn1,
  myColumn2
) VALUES (
  100,
  'value1',
  'value2'
);