CODEWITHSUNDEEP
mysql
Will
Will

Reputation: 5590

mySQL "REPLACE INTO" : is it having any effect in this case

I'm charged with porting some old code to a new platform and I'm looking at a bit that has me pretty puzzled. I think I'm familiar with what REPLACE INTO does, but this leaves me feeling unsure. The mySQL query is:

REPLACE INTO theTable SET value1 = ?, value2 = ?

My understanding is this only has an effect if those columns are defined as unique. The table definition for theTable looks like this:

CREATE TABLE `theTable` (
  `value1` int(11) unsigned DEFAULT NULL,
  `value2` int(11) unsigned DEFAULT NULL,
  KEY `value1` (`value2`),
  KEY `value2` (`value2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I've inserted a row that's an exact duplicate of an existing row and had nothing happen ( a new row was inserted, the old row still exists ) ... am I missing something or did the original developer just do something that was completely useless?

Upvotes: 1

Views: 221

Answers (2)

jhurtado
jhurtado

Reputation: 8747

From MySQL manual:

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...

... Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

So you need to add UNIQUE KEY in order for it to work.

Upvotes: 3

Mchl
Mchl

Reputation: 62395

Like you said: it will only replace (in fact delete + insert) row, if UNIQUE constraint is in place. In your table there is none.

Upvotes: 3

Related Questions