SQL Insert data already exists, perform update update

In many projects, we need to make constant calls and updates to the data and add it to the database if new data comes in. One of the most important is that we do not know whether the data passed in the original database is already in the record, so we often need to crawl data to determine if there is, if there is an implementation of update, if there is no execution insert, this will require two database operations, The first time is the query, the second is update or INSERT, is there a way to only need to do one operation? The answer is yes.

INSERT ... The on DUPLICATE KEY UPDATE method can help us to solve this problem very well. Let's take a look at an example:

INSERT into table (a,b,c) VALUES (1,2,3) on DUPLICATE KEY UPDATE b=b-1,c=c+1;

This statement can be implemented, when inserting (1,2,3) This record, check whether there is a=1, if there are A=1 records, then update a=1 this record, the equivalent of executing the following statement:

UPDATE table SET b=b-1,c=c+1 WHERE a=1;

If the a=1 record does not exist in the database, the new record is inserted.

Use Insert ... On DUPLICATE key Update if the A field is set to the unique key index, the method is not valid.

Let's look at some examples.

The statement is based on a unique index or primary key, for example, a field A is added with a unique index, and a record value of 1 already exists in the table, and the following two statements have the same effect:

INSERT into table (a,b,c) VALUES (1,2,3)
On DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

On DUPLICATE KEY Update, you can place multiple fields, separated by commas in English. With on DUPLICATE KEY UPDATE, if a new row is inserted, the number of rows affected is 1, and if an existing row of data is modified, the number of rows affected is 2.

If field B is also added with a unique index, the statement is equivalent to the following UPDATE statement:

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

If A=1 OR b=2 matches multiple rows, only one row is modified. Typically, in the on DUPLICATE KEY UPDATE statement, we should avoid multiple unique indexes. If you need to insert or update more than one piece of data, and the updated fields need to be based on other fields, you can use the following statement:

INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6)
On DUPLICATE KEY UPDATE c=values (a) +values (b);
After on DUPLICATE KEY update, use the values () method, which is equivalent to the following two statements:

INSERT into table (a,b,c) VALUES (1,2,3)
On DUPLICATE KEY UPDATE c=3;--1+2
INSERT into table (a,b,c) VALUES (4,5,6)
On DUPLICATE KEY UPDATE c=9;--4+5

If a table contains a auto_increment field, each time you insert the data, you can use the last_insert_id () method to return the last auto-generated value, if the insert ... On DUPLICATE KEY UPDATE statement modifies a piece of data, then the value obtained by the last_insert_id () method will be incorrect, the actual test is more than a number, such as adding 3 data to the table, then through the last_insert_id () Method gets a value of 3, but after modifying a piece of data through the statement, the value obtained by the last_insert_id () method is 4. If you want to resolve the problem, you can use the following statement:

INSERT into table (a,b,c) VALUES (1,2,3)
On DUPLICATE KEY UPDATE id=last_insert_id (id), c=3;
The point is this id=last_insert_id (ID).

There is another way to use ignore, and let's take a look at an example:

INSERT ignore into a (ID, type) VALUES (11, 22)

Ignore is used in this statement, meaning that if there is a record in the database id=11, the insert operation is not performed (ignored) and the insertion is performed only if the above conditions are not satisfied. The Ignore method also requires that the ID be a unique key (the primary key defaults to be a unique key, so the ID can be a primary key)

In addition, another method is replace into, which is used in the same way as insert into, but unlike the above ignore effect, if id=11 already exists in the database, the type of the record is forced to replace id=11 22.

See an example

The following are the differences between code descriptions, as follows:

CREATE TABLE TESTTB (
ID int NOT NULL PRIMARY key,
Name varchar (50),
Age int
);
Insert into TESTTB (id,name,age) VALUES (1, "BB", 13);
SELECT * from TESTTB;
Insert Ignore into TESTTB (Id,name,age) VALUES (1, "AA", 13);
The SELECT * from testtb;//is still 1, "BB", 13, because the ID is the primary key, the primary key is duplicated but the error is ignored when the ignore is used
Replace into TESTTB (id,name,age) VALUES (1, "AA", 12);
SELECT * from TESTTB; Data into 1, "AA", 12

To sum up:

If you want to implement data when inserting data, and if so, replace the other fields of the record, we can use three methods to determine if there is a record of the corresponding key when inserting the data, insert ... On DUPLICATE KEY UPDATE, insert gnore into and replace into. Where insert ... On DUPLICATE key update and replace into enables you to replace the other fields of the record if a record of the corresponding key already exists.