CODEWITHSUNDEEP
mysqlsqlreplace
Andres SK
Andres SK

Reputation: 10974

Using REPLACE in MySQL is not actually replacing row

This is my current table with some rows in it:

CREATE TABLE `user_versus` (
  `id_user_versus` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_winner` int(10) unsigned NOT NULL,
  `id_user_loser` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `date_versus` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user_versus`),
  KEY `id_user_winner` (`id_user_winner`,`id_user_loser`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

INSERT INTO `user_versus` (`id_user_versus`, `id_user_winner`, `id_user_loser`, `id_user`, `date_versus`) VALUES
(1, 6, 7, 1, '2013-10-25 23:02:57'),
(2, 6, 8, 1, '2013-10-25 23:02:57'),
(3, 6, 9, 1, '2013-10-25 23:03:04'),
(4, 6, 10, 1, '2013-10-25 23:03:04'),
(5, 6, 11, 1, '2013-10-25 23:03:10'),
(6, 6, 12, 1, '2013-10-25 23:03:10'),
(7, 6, 13, 1, '2013-10-25 23:03:18'),
(8, 6, 14, 1, '2013-10-25 23:03:18'),
(9, 7, 6, 2, '2013-10-26 04:02:57'),
(10, 8, 6, 2, '2013-10-26 04:02:57'),
(11, 9, 8, 2, '2013-10-26 04:03:04'),
(12, 9, 10, 2, '2013-10-26 04:03:04'),
(13, 9, 11, 2, '2013-10-26 04:03:10');

Now, I want to insert or replace the last row with this query:

REPLACE INTO user_versus (id_user_winner, id_user_loser, id_user) VALUES (9, 14, 2)

When I do that, instead of replacing the last row (that holds those exact values) it is adding a new row. Why? Thanks!

Upvotes: 1

Views: 68

Answers (4)

Mike Brant
Mike Brant

Reputation: 71384

This is because you are not specifying the primary key, which is id_user_versus in your insert.

Perhaps you intend to have a unique key (rather than just a standard key) across id_user_winner and id_user_loser in which case your replace would work to replace the row with those values.

Upvotes: 1

Vatev
Vatev

Reputation: 7590

REPLACE uses UNIQUE CONSTRAINT's (unique indexes) to determine which rows are 'duplicate'.

In your table the only unique constraint is the PRIMARY KEY, and it is not specified in the INSERT.

Upvotes: 1

edwardmp
edwardmp

Reputation: 6591

The REPLACE statement only replaces a row if an existing row with the same primary key and unique keys is found.

The MySQL documentation specifies:

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.

Source: http://dev.mysql.com/doc/refman/5.1/en/replace.html

The primary key in this table is not specified in your query, so it won't replace that row.

Upvotes: 0

bhamby
bhamby

Reputation: 15440

REPLACE "matches" on the PRIMARY KEY (or UNIQUE index) of the table. I don't see your primary key (id_user_versus) in your insert statement.

Upvotes: 0

Related Questions