John Smith John Smith - 2 months ago 7
MySQL Question

MySQL, REPLACE INTO without deleting?

I have this table structure:

ID RATE1 RATE2 RATE3


and its empty. Lets give a new rate:

REPLACE INTO rates (ID, RATE2) VALUES (99, 4);


the table will be:

99, 0, 4, 0


so far so good. Another rating takes place:

REPLACE INTO rates (ID, RATE3) VALUES (99, 2);


and this is where the error happens. I get

99, 0, 0, 2


instead of:

99, 0, 4, 2


I wanted to retain RATE2 too. Is there a workaround for this?

Answer

Use INSERT...ON DUPLICATE KEY UPDATE instead of REPLACE.

INSERT INTO ThisTable (id, rate3) VALUES (99, 2) 
  ON DUPLICATE KEY UPDATE rate3=VALUES(rate3);

This leaves the other columns of an existing row as they were.

Demo: http://sqlfiddle.com/#!9/0ae4a

Comments