Mysql Tutorial: Considerations for ON DUPLICATE KEY UPDATE

MySQL versions later than MySQL 4.1 Support INSERT... The on duplicate key update syntax allows you to execute three SQL statements (SELECT, INSERT, UPDATE) and reduce them to one statement.

For example, the ipstats table structure is as follows:

Reference

Create table ipstats (
Ip VARCHAR (15) not null unique,
Clicks SMALLINT (5) unsigned not null default '0'
);

Originally, three SQL statements were required:

IF (SELECT * FROM ipstats WHERE ip = '192. 168.0.1 '){
UPDATE ipstats SET clicks = clicks + 1 WHERE ip = '192. 168.0.1 ';
} Else {
Insert into ipstats (ip, clicks) VALUES ('192. 168.0.1 ', 1 );
}

Now, you only need to run the following SQL statement:

Insert into ipstats VALUES ('192. 168.0.1 ', 1) on duplicate key update clicks = clicks + 1;

Note: To use this statement, the table must have a unique index or primary key.