Update if insert already exists when insert record is not present in MySQL

If you specify on DUPLICATE key UPDATE and the insert row causes duplicate values to occur in a unique index or primary key, the

Row old row update. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

The code is as follows:

The code is as follows	Copy Code
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;

Instance 1

The code is as follows

Copy Code

mysql> truncate ' 200702 '; Query OK, 0 rows affected (0.01 sec) Mysql> select * from ' 200702 '; Empty Set (0.01 sec) mysql> insert INTO ' 200702 ' (' Domain ', ' 2nd_domain ', ' TLD ', ' query_ns1 ', ' query_ns2 ', ' report_date ') VALUES (' dnspod.com ', ' dnspod ', ' com ', 1000, Watts, ' 2007-02-04 ') on DUPLICATE KEY UPDATE ' query_ns1 ' = ' query_ns1 ' + 1000, ' query_ns2 ' = ' query_ns2 ' + 2000; Query OK, 1 row Affected (0.00 sec) Mysql> select * from ' 200702 '; +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ | ID | Domain | 2nd_domain | TLD | query_ns1 | Query_ns2 | QUERY_NS3 | QUERY_NS4 | Report_date | +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ | 1 | dnspod.com | Dnspod | com | 1000 | 2000 | 0 | 0 | 2007-02-04 | +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ 1 row in Set (0.00 sec) mysql> insert INTO ' 200702 ' (' Domain ', ' 2nd_domain ', ' TLD ', ' query_ns1 ', ' query_ns2 ', ' report_date ') VALUES (' dnspod.com ', ' dnspod ', ' com ', 1000, Watts, ' 2007-02-04 ') on DUPLICATE KEY UPDATE ' query_ns1 ' = ' query_ns1 ' + 1000, ' query_ns2 ' = ' query_ns2 ' + 2000; Query OK, 2 rows affected (0.01 sec) Mysql> select * from ' 200702 '; +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ | ID | Domain | 2nd_domain | TLD | query_ns1 | Query_ns2 | QUERY_NS3 | QUERY_NS4 | Report_date | +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ | 1 | dnspod.com | Dnspod | com | 2000 | 4000 | 0 | 0 | 2007-02-04 | +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+ 1 row in Set (0.01 sec) Mysql>

Of course, when building a table, don't forget to give Domain a unique

The code is as follows	Copy Code
UNIQUE KEY ' domain ' (' domain ', ' report_date ')

key exists is updated, does not exist insert

The code is as follows	Copy Code
Insert INTO ... on duplicate key update

For operations that need to be based on the original record. If the Icount field in the table is used for counting, and when there is no record, the value of insert is 0, and when there is a record, value needs

Update to Value+1, then replace will not be able to complete this function. Use INSERT, its basic syntax is insert INTO ... on duplicate

Key update ..., if the above statement is

The code is as follows	Copy Code
INSERT into T_test set ikey=1,value= ' a ' and value2=1 on duplicate key update value2=value2+1;

If there is more than one unique index in the table, add a unique key to the Value field. When the table has Ikey and value two unique indexes, replace will put all the

The data item with the same unique index value is deleted, and the new record is inserted. If there are two records in the table

The code is as follows	Copy Code
+------+-------+--------+ | Ikey | Value | icount | +------+-------+--------+ | 2 |    A | 10 | | 1 |    B | 40 | +------+-------+--------+

The replace into t_test set ikey=1,value= ' A ', icount=0, deletes all two records in the table, and then inserts a new record.
The INSERT into T_test set ikey=1,value= ' a ' and icount=0 on duplicate key update icount=icount+1 updates only one

Records. The effect is equivalent

code is as follows	copy code
update t_test Set icount=icount+1 where Ikey=1 or value= ' a ' limit 1;