MySQL Insert update _on DUPLICATE KEY update

Source: Internet
Author: User
Tags mysql insert

Premise: The action table has a primary key or a unique index

INSERT INTO: The table does not have a corresponding record, then inserted, if there is a corresponding record, then error;

INSERT INTO IGNORE: The corresponding record does not exist in the table, it is inserted, if there is a corresponding record, it is ignored and no action is made

REPLACE into: The table does not have a corresponding record, then insert, if there is a corresponding record, delete the original record, and then insert a new record.

On DUPLICATE key update (on DUPLICATE key update) functions like replace into:

Insert a new record directly if there is no data in the table (primary key value or unique index value for the record to be inserted);

If the data already exists in the table (the primary key or unique index value is the same), the update operation is performed.

So, on DUPLICATE KEY update and replace into are different during operation:

When data already exists, on DUPLICATE KEY Update performs an update operation instead of deleting the original record before inserting a new record.

Statement: INSERT into table_name (field1,field2 ...) VALUES (value1,value2 ...) on duplicate key update filed3=value3;

Description: The statement after update can be another expression.

Suppose you have one of the following table:

Mysql>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli |3600||4| Xiaohua |6000|+----+----------+-------+4Rowsinch Set(0.00sec) MySQL>describe Staff_3;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID |int( One)  | NO | PRI | NULL | auto_increment | | name |Char(Ten) | YES | UNI |                NULL | || Slary |int( One)  |     YES | |                NULL | |+-------+----------+------+-----+---------+----------------+3Rowsinch Set(0.00Sec

On the duplicate key on command: (as can be seen from the ID, the command performs the update operation do not waste the self-increment property value)

mysql> INSERT INTO Staff_3 (name,slary) VALUES ('Xiaoli',4000) on duplicate key update slary= the; Query OK,2Rows Affected (0.00sec) MySQL>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli | the||4| Xiaohua |6000|+----+----------+-------+4Rowsinch Set(0.00sec) MySQL> INSERT into Staff_3 (name,slary) VALUES ('xiaoming',4000) on duplicate key update slary= the; Query OK,1Row affected (0.00sec) MySQL>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli | the||4| Xiaohua |6000||5| Xiaoming |4000|+----+----------+-------+5Rowsinch Set(0.00Sec

MySQL Insert update _on DUPLICATE KEY update

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.