The difference between Replace INTO and INSERT INTO

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The difference between Replace INTO and INSERT INTO:


Operation and INSERT REPLACE are very similar. With one exception, if an old records in the table have the same value and one for the new record of PRIMARY KEY or a UNIQUE index, before is inserted in the new record, the old record is deleted.

Note that, unless the table has a PRIMARY KEY or UNIQUE index, otherwise, no significance using a REPLACE statement. This statement will be the same with INSERT, because no index is used to determine whether a copy of the other line.


All column values are the value specified by the REPLACE statement in itself. All the missing columns are set to the default value of their own, just like the INSERT. You cannot reference values from the current line, also cannot use in the new row values. If you use a “ SET col_name = col_name + 1” assignment, is a reference to the column name in the right will be used as DEFAULT (col_name) treatment. Therefore, the assignment is equivalent to the SET col_name = DEFAULT(col_name) + 1.


In order to be able to use REPLACE, you must also have a table of INSERT and DELETE permissions.


The REPLACE statement returns a number, number to indicate the affected rows. This is the number of rows are deleted and inserted and be. If for a single REPLACE the number is 1, a row is inserted, and no rows are deleted. If the number is greater than 1, is inserted in a row, one or more of the old rows are deleted. If the table contains more than one unique index, and a copy of different old row in a unique index in different values, there may be a single line to replace many old line.


The number of rows affected can easily determine whether REPLACE adds only a line, or whether the REPLACE has replaced the other line: check the number is 1 (add) or greater (replacement).


If you are using C API, you can use the mysql_affected_rows () function to obtain the number of rows affected.


At present, you are not in a subquery, changing to a table, and choose from the same table.


Algorithm details below when this algorithm is used for LOAD (DATA…REPLACE):


1 try to put the new row into a table


2 as for the primary key or unique key words repeated key error caused by the insertion failed:


A. delete contains conflict for repeat key values from the table


B. try again to new rows into a table


Use the following format:


REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},…),(…),…

Or:


REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

Or:


REPLACE [LOW_PRIORITY | DELAYED]

[INTO] tbl_name [(col_name,...)]

SELECT …

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Kenneth at November 22, 2013 - 9:22 AM