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

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

Example:

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

Create Custom Page Tabs for Facebook Page

Posted on 7 years ago

Bhumi

How you can write Custom Queries in Modx

Posted on 7 years ago

Bhumi

New Features in MySQL 5.6

Posted on 7 years ago

Bhumi

Hello world

Updated 1 month ago

Nick Carter

In depth Understanding of Laravel Core

Updated 3 months ago

Bhumi

ECMAScript 6 New Features – 2

Updated 1 year ago

Bhumi

The Reader’s Poll – June 2015

Posted on 4 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 5 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 6 years ago

Bhumi