Using REPLACE in MySQL is not actually replacing row

Question

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!

Mike Brant · Accepted Answer · 2013-11-20 23:00:03Z

1

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.

answered Nov 20, 2013 at 23:00

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andres SK Over a year ago

This was the most complete answer. It worked. Thanks!

Vatev · Accepted Answer · 2013-11-20 22:59:58Z

1

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.

answered Nov 20, 2013 at 22:59

Vatev

7,5801 gold badge36 silver badges42 bronze badges

Comments

bhamby · Accepted Answer · 2013-11-20 22:59:04Z

0

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.

answered Nov 20, 2013 at 22:59

bhamby

15.6k1 gold badge49 silver badges67 bronze badges

Comments

edwardmp · Accepted Answer · 2013-11-20 22:59:15Z

0

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.

answered Nov 20, 2013 at 22:59

edwardmp

6,6316 gold badges53 silver badges80 bronze badges

Collectives™ on Stack Overflow

Using REPLACE in MySQL is not actually replacing row

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related