mysql REPLACE INTO only some fields

Question

I have a mysql table with

CREATE TABLE `gfs` (
    `localidad` varchar(20),
    `fecha` datetime,
    `pp` float(8,4) NOT NULL default '0.0000',
    `temp` float(8,4) NOT NULL default '0.0000',
    PRIMARY KEY (`localidad`,`fecha`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

when I try update a field with this

REPLACE INTO gfs(localidad,fecha,pp) VALUES ("some_place","2012-08-05 02:00","1.6")

the previous value en temp is lost. why ?

Answer 1

As documented under REPLACE Syntax and mentioned already by others:

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

The manual goes on to explain:

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.

Therefore, you want:

INSERT INTO gfs (localidad, fecha, pp)
VALUES ('some_place', '2012-08-05 02:00', 1.6)
ON DUPLICATE KEY UPDATE pp=VALUES(pp);

Answer 2

Because it's a REPLACE statement not an UPDATE. When replacing you'll get the unspecified values as the default column value.

Updating allows you to change the previous value and also operate on that value without having selected it beforehand (SET count = count+1). It allows all previously set values to remain set. That's what you want to do.