Mysql REPLACE only in 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 to update a field using this

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

the previous value of en temp is lost. What for?

Answer 1

As described in REPLACE Syntax and already mentioned by others:

REPLACE is an extension of the MySQL SQL standard. It either inserts or deletes and inserts. For another MySQL extension for standard SQL that either inserts or updates, see Section 13.2.5.3, " INSERT ... ON DUPLICATE KEY UPDATE Syntax" .

The manual further explains:

The values ​​for all columns are taken from the values ​​specified in the REPLACE statement. Any missing columns have default values, as is the case for INSERT . You cannot reference values ​​from the current line and use them in a new line.

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 this is a REPLACE UPDATE not UPDATE . When replacing, you will receive unspecified values ​​as the default column value.

The update allows you to change the previous value and also work with this value without first selecting it ( SET count = count+1 ). It allows you to save all previously set values. This is what you want to do.

Answer 3

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

REPLACE works exactly the same as INSERT, except that if the old row in the table has the same value as the new row for the PRIMARY KEY or UNIQUE index, the old row is deleted before inserting a new row.

Are you trying instead of UPDATE ?