REPLACE statement with embedded IF() logic?

EDIT: This actually works fine, no idea why I thought otherwise.

I have a prices table which includes a column price_开发者_如何学JAVAwas which needs to contain the highest ever value for prices.

Is it possible to do a REPLACE query which would update this if required?

The following (which is simplified and built dynamically in PHP) doesn't seem to work.

REPLACE prices
SET     price = 1.99,
        price_was = IF(1.99 > price_was, 1.99, price_was)
        id_product = 1

I'm thinking perhaps it's not possible, but would love to hear otherwise since I'm updating many records and need to be as efficient as possible.

---

The query you posted is indeed valid, try it for yourself. I would use an UPDATE though since you're only updating one field and the REPLACE can possible over-write other column data you want left alone.

---

Try INSERT ... ON DUPLICATE KEY UPDATE instead:

INSERT INTO prices (price, price_was, id_product)
   VALUES (1.99, 1.99, 1)
ON DUPLICATE KEY UPDATE 
   price_was = IF(VALUES(price) > price_was, VALUES(price), price_was)
   id_product = VALUES(id_product)

This will do either an INSERT or an UPDATE, while the REPLACE statement does either an INSERT or a DELETE followed by an INSERT. You are not able to reference old values in a REPLACE statement, probably because of the DELETE/INSERT semantics. From the docs:

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.