The REPLACE statement is a MySQL addition to the SQL, and it is used or could be used to insert and update data in database tables. The REPLACE query work similarly to INSERT with the introduction of two rules. These are:

  • If the row (record) we want to replace (and thus insert) does not exist, the REPLACE statement will add a new record to the database table;
  • If the record already exists, the REPLACE statement will first delete the existing record, and then add the new one.

Syntax

REPLACE INTO table_name (column_name1,column_name2,...) VALUES(value1,value2,...);

Following examples show a more realistic application:

Example

Inserting a new row (or replacing the old one) in the dept table:

REPLACE INTO dept (deptno, dname, loc) VALUES (50,'DEVELOPMENT', 'DETROIT');

Notes:
  1. The REPLACE statement is not the same as REPLACE string function.
  2. In order to use the REPLACE statement, the privileges status has to be at least same as for INSERT and DELETE.