Back to Top

How to use Replace function in MySQL

In MySQL, To modify record we mostly prefer to use UPDATE command and you all have an idea about UPDATE command so let’s see another method for modifying records is REPLACE command which is extremely similar to the INSERT command.


REPLACE INTO table_name (column list) VALUES (column values);

The REPLACE Command works like if the record you are inserting into the table contains a primary key value that matches a record already in the table, the record in that table will be deleted and the new record inserted in its place.

So I can say that The REPLACE command perform DELETE action and re-INSERT particular record.Like, you get two commands within MySQL for the price of one. 🙂


REPLACE INTO products VALUES (1, 'Apple', '50','');
2 rows affected. ( query took 0.0346 sec)

Above REPLACE Command example is replaces the entry for products which have id 1.Notice that above query result,which is 2 rows affected because first row id is primary key and that had a matching value in products table then actual row was deleted and the new row will be added into the table.

If you use a REPLACE Command and the value of the primary key in the new record does not match a value for a primary key already in the table then the record would be inserted and only one row would be affected.

Suggested Reading:

To learn MySQL PACK_KEYS with example
Table Optimization in MySQL
MySQL coalesce() function

I hope you have enjoyed this article. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to add Sorting columns in Joomla

Posted on 6 years ago


How To Repair MySQL Table

Posted on 7 years ago


The Reader’s Poll – June 2015

Posted on 3 years ago


The Reader’s Poll – August 2014

Posted on 4 years ago


The Reader’s Poll – June 2014

Posted on 5 years ago


The Readers’ Poll – May 2014

Posted on 5 years ago


The Readers’ Poll – August 2013

Posted on 5 years ago