[]ON DUPLICATE KEY UPDATE

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

MySQL when inserted into the record does not exist, the presence of updates when recording

MySQL Insert the record does not exist, the presence of updates when recording

Reprint.:


The Internet has three basic solutions.

The first:

Example 1: insert records

Suppose there is a primary key for client_id clients table, you can use the following statement:

INSERTINTOclients
(client_id,client_name,client_type)
SELECTsupplier_id,supplier_name,'advertising'
FROMsuppliers
WHEREnotexists(select*fromclients
whereclients.client_id=suppliers.supplier_id);

Example 1: insert a single record

INSERTINTOclients
(client_id,client_name,client_type)
SELECT10345,'IBM','advertising'
FROMdual
WHEREnotexists(select*fromclients
whereclients.client_id=10345);

Use dual to do the table name allows you to insert fields directly with the value in the select statement, even if the value does not exist in the current list.

Second:

INSERTON DUPLICATE KEY UPDATEUse

If you specify ON DUPLICATE KEY UPDATE, and insert rows will result in a UNIQUEIndexesOr PRIMARY KEY duplicate values in the old, executing UPDATE. For example, if the column a was defined as UNIQUE, and contains the value 1, then the two statements that have the same effect:

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
          ->ON DUPLICATE KEY UPDATE c=c+1;
mysql>UPDATE table SET c=c+1 WHERE a=1;

If the row is inserted as a new record, are affected by the values of the row is 1; if the original records are updated, the affected rows value is 2.

Note: if the column B is the only listed, the INSERT and the UPDATE statement a:

mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If the a=1 OR b=2 and a plurality of rows to the match, only one row is updated. Usually, you should try to avoid using the ON DUPLICATE KEY clause on a unique key table.

You can use the VALUES in the UPDATE clause(col_name)FunctionThe referencing column values from the INSERT part of the INSERT... UPDATE statement. In other words, if there is no repeat keywords conflict, the UPDATE clause in the VALUES (col_name) can be inserted col_name reference value. This function is especially suitable for multi line insertion. VALUES () function has meaning only in the INSERT... UPDATE statement, other times will return NULL.

Example:

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
          ->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

This statement is the same with the following two statements effect:

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
          ->ON DUPLICATE KEY UPDATE c=3;
mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)
          ->ON DUPLICATE KEY UPDATE c=9;

When you use the ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.

Third:

The REPLACE statement

  We useDatabaseMay often encounter this kind of situation. If a table is a unique index on a field, when we go to the table to use the existing key insert a record, it will throw a primary key conflict error. Of course, we may want to use the new record value to cover the original record values. If you use the traditional approach, must use the DELETE statement to delete the original records, and then use INSERT to insert a new record. While MySQL provides a new solution for us, this is the REPLACE statement. Use REPLACE to insert a record, if not repeat, REPLACE and INSERT function, if there are duplicate records, REPLACE will use the new record value to replace the original record values. 

  The biggest advantage of using REPLACE is that DELETE and INSERT can be Become, forming an atomic operation. This can not be considered in using both DELETE and INSERT add affairs complex operation. 

  When using REPLACE, the table must have a unique index, and the index of field cannot allow null values, or REPLACE and INSERT exactly the same. 

  In the implementation of REPLACE, the system returns the number of rows affected, if the return of 1, as illustrated in table does not duplicate records, if return to 2, that there is a duplicate records, the system automatically to call the DELETE to delete the record, and then record with INSERT to insert a record. If the return value is greater than 2, it shows how a unique index, there are many records are deleted and inserted. 

  The syntax of REPLACE and INSERT are very similar, the REPLACE statement as the following is to insert or update a record. 

  REPLACE INTO users (id,name,age) VALUES(123, 'Zhao Benshan', 50);
 
  Insert a record: 

  REPLACE INTO users(id, name, age)
  VALUES(123, 'Zhao Benshan', 50), (134,'Mary',15);

  REPLACE can also use the SET statement

  REPLACE INTO users SET id = 123, name = 'Zhao Benshan', age = 50;

  The above mentioned that REPLACE may affect more than 3 records, this is because there is a unique index more than one in the table. In this case, REPLACE will consider each one unique index, and duplicate records for each index are deleted, and then insert a new record. Suppose you have a table1 table, there are 3 fields of a, B, C. They have a unique index. 

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

  Assume that table1 has 3 records

  a b c
  1 1 1
  2 2 2
  3 3 3

  We use the REPLACE statement to insert a record into the table1. 

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

  The returned results are as follows

  Query OK, 4 rows affected (0.00 sec)

  Records in the table1 as follows

  a b c
  1 2 3

  We can see, the REPLACE will be 3 original records are deleted, and then the (1, 2, 3) insert. 
Summary: Although no specific test, feeling the first most cost resources (feel), but if you do not have a primary key words can use him. 
Second and third is the difference between: 
1)Insert is the first attempt to insert, update if a primary key exists. REPLACE is the first attempt to insert, delete the original record exists if a primary key is inserted into the. 
2)If there is a unique key conflict (conflict different keyword occurs in different recording), for example, now there are 2 fields of 2 records conflict (no record conflict a field), 
The insert is the choice of the sorted in front of a update, REPLACE is to remove the two records, and then insert a new record. 
A little bit of my opinion, if there is an error, please correct me. 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Douglas at December 12, 2013 - 6:07 AM