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
1 2 3 | 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:
1 2 3 4 | 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.
