CODEWITHSUNDEEP
mysqlreplace
JuanPablo
JuanPablo

Reputation: 24774

mysql REPLACE INTO only some fields

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 ?

Upvotes: 10

Views: 25133

Answers (4)

Antony
Antony

Reputation: 4344

I experienced the same and was glad to see there is not an immediately obvious solution. I must admit whilst ON DUPLICATE KEY is a solution, replace seems much simpler/nicer. So (assuming just one field as per the question), if you don't have too many fields to persist the following will work, use replace and keep the existing values.

SET @existing_temp = (SELECT temp FROM gfs WHERE localidad = ?);

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

This uses a local variable and then includes it in your replace query. Obviously this uses another query, but because of indexing is relatively cheap.

Upvotes: 0

eggyal
eggyal

Reputation: 125875

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);

Upvotes: 28

Mihai Stancu
Mihai Stancu

Reputation: 16107

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.

Upvotes: 4

NotGaeL
NotGaeL

Reputation: 8484

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

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

Are you trying to do an UPDATE instead?

Upvotes: 2

Related Questions