MySQL on DUPLICATE KEY update repeated insert update

MySQL Update method when inserting duplicates:

The first method:

Example one: inserting multiple records

Suppose you have a clients table with a primary key of client_id, you can use the following statement:

INSERT  into clients (Client_id,client_name,client_type) SELECT supplier_id,supplier_name,'advertising' from Suppliers  wherenotexists(select*fromWHERE clients.client_id=suppliers.supplier_id);

Example one: inserting a single record

INSERT  intoclients (Client_id,client_name,client_type)SELECT 10345,'IBM','Advertising' fromDualWHERE  not exists(Select *  fromClientswhereclients.client_id=10345);

Using dual to make a table name allows you to follow the SELECT statement directly following the value of the field you want to insert, even if the value does not already exist in the current table.

The second method:

INSERT in on DUPLICATE key update use (this article highlights)

If you specify an on DUPLICATE KEY update and the insert row causes duplicate values to appear in a unique index or primary KEY , the old line UPDATE is performed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

Mysql>INSERT  into Table(A,B,C)VALUES(1,2,3) onDUPLICATEKEY UPDATEC=C+1; MySQL>UPDATE Table SETC=C+1 WHEREA=1;

If the row is inserted as a new record, the value of the affected row is 1, and if the original record is updated, the value of the affected row is 2.

NOTE: If column B is also the only column, the insert is equivalent to this UPDATE statement:

MySQL>UPDATEtableSET c=c+1WHERE a  =1OR b=21;

If A=1 OR b=2 matches multiple rows, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on a table with multiple unique keywords.

You can use the values (col_name) function from the Insert ... in the UPDATE clause. The insert portion of the UPDATE statement refers to the column value. In other words, if a duplicate keyword conflict does not occur, values (col_name) in the update clause can refer to the value of the col_name being inserted. This function is especially useful for multi-row insertions. The VALUES () function is only in the insert ... The UPDATE statement makes sense, and returns null at other times.

Example:

 mysql>  insert  into  table  (a,b,c) values  (1 , 2 , 3 ), (4 , 5 ,  6  )  on  DUPLICATE update  C=  values  (a) +  values  (b); 

This statement works the same as the following two statements:

Mysql>INSERT  into Table(A,B,C)VALUES(1,2,3)           - onDUPLICATEKEY UPDATEC=3; MySQL>INSERT  into Table(A,B,C)VALUES(4,5,6)           - onDUPLICATEKEY UPDATEC=9;

When you use the on DUPLICATE KEY update, the delayed option is ignored.

The third method:

Replace statement

We may encounter this situation frequently when working with databases. If a table has a unique index on a field, when we insert a record into the table with a key value that already exists, it will throw a primary key conflict error. Of course, we might want to overwrite the original record value with the value of the new record. If you use the traditional approach, you must first delete the original record by using the DELETE statement, and then insert the new record using insert. And in MySQL we provide a new solution, which is the replace statement. When inserting a record with replace, replace is the same as the Insert function, and replace replaces the original record value with the value of the new record if it is duplicated.

The biggest benefit of using replace is that you can combine delete and insert to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using delete and insert.

When using replace, the table must have a unique index, and the field in which the index resides cannot allow null values, otherwise the replace is exactly the same as the insert.

After you perform the replace, the system returns the number of rows affected, and if 1 is returned, indicating that there are no duplicate records in the table, and if 2 is returned, a duplicate record is called, and the system automatically calls the delete to delete the record, and then records inserts to insert the record. If the value returned is greater than 2, it indicates that there are multiple unique indexes, and that multiple records are deleted and inserted.

The syntax for replace is very similar to insert, as the following replace statement inserts or updates a record.

REPLACE into Users (id,name,age) VALUES (123, ' Zhao Benshan ', 50);

　

Insert more than one record:

REPLACE into users (ID, name, age)

 　　 VALUES(123,'Zhao Benshan', -), (134,'Mary', the); Replace can also use the SET statementREPLACE  intoUsersSETId= 123, name= 'Zhao Benshan', age=  -;

The above mentioned that replace may affect more than 3 records because there is more than one unique index in the table. In this case, replace takes each unique index into account and deletes the duplicate records for each index, and then inserts the new record. Suppose there is a Table1 table with 3 fields A, B, C. They all have a unique index.

CREATE TABLE table1 (a int not null UNIQUE,B int NOT NULL UNIQUE,C int. NOT NULL UNIQUE);

Let's say there's 3 records in Table1.

A b C

1 1 1

2 2 2

3 3 3

Below we use the Replace statement to insert a record into the Table1.

REPLACE into Table1 (A, B, c) VALUES (All-in-all);

The results returned are as follows

Query OK, 4 rows Affected (0.00 sec)

The records in Table1 are as follows

A b C

1 2 3

< turn >http://lobert.iteye.com/blog/1604122

MySQL on DUPLICATE KEY update repeated insert update