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.

SYNTAX

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

Example:

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

The Readers’ Poll – January 2013

Posted on 5 years ago

Bhumi

How to Create ShortCode in WordPress

Posted on 6 years ago

Bhumi