REPLACE INTO, does it re-use the PRIMARY KEY?

mysql sql duplicates sql-insert sql-delete
matt picture matt · Aug 30, 2012 · Viewed 17k times · Source

The REPLACE INTO function in MySQL works in such a way that it deletes and inserts the row. In my table, the primary key (id) is auto-incremented, so I was expecting it to delete and then insert a table with id at the tail of the database.

However, it does the unexpected and inserts it with the same id! Is this the expected behaviour, or am I missing something here? (I am not setting the id when calling the REPLACE INTO statement)

Answer

hol picture hol · Aug 30, 2012

This is an expected behavior if you have another UNIQUE index in your table which you must have otherwise it would add the row as you would expect. See the documentation:

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. See Section 13.2.5, “INSERT Syntax”.

https://dev.mysql.com/doc/refman/5.5/en/replace.html

This really also makes lot of sense because how else would mySQL find the row to replace? It could only scan the whole table and that would be time consuming. I created an SQL Fiddle to demonstrate this, please have a look here

Related questions

Delete all Duplicate Rows except for One in MySQL?

How would I delete all duplicate data from a MySQL Table? For example, with the following data: SELECT * FROM names; +----+--------+ | id | name | +----+--------+ | 1 | google | | 2 | yahoo | | 3 | msn | | 4 | google | | 5 | google | | 6 | yahoo | +----+--------+ I would use SELECT DISTINCT name …

Read More
Remove duplicate rows in MySQL

I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL …

Read More
MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

I have a SQL query where I want to insert multiple rows in single query. so I used something like: $sql = "INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29)"; mysql_query( $sql, $conn ); The problem is …

Read More