MySQL, REPLACE INTO without deleting?

Question

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 1

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