mysql ON DUPLICATE KEY UPDATE statement example

MySQL has supported INSERT since version 4.1... The ON DUPLICATE KEY UPDATE syntax reduces the need to execute three SQL statements (SELECT,INSERT,UPDATE) to one statement.
For example, the STRUCTURE of ipstats table is as follows:

CREATE TABLE ipstats (
ip VARCHAR(15) NOT NULL UNIQUE,
clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0'
);
 

Three SQL statements should have been executed, as follows:

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 the following SQL statement to complete:

INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;
 

Note that to use this statement, the table must have a unique index or primary key.
To sum up:
1. Both replace and insert*update are identical to insert if there is no primary key record in the table.
2. If primary key records exist in the table, replace is equivalent to delete and insert, while insert*update is equivalent to if exist do update else do insert. Therefore, if THE fields filled by replace are not complete, the fields that have not been updated will be modified to the default value, and if there are auto-increasing id, the auto-increasing id will be changed to the latest value (which may result in record loss if marked by auto-increasing id). insert*update updates only a few fields and does not change fields that are not updated (it does not force changes to tacit values).
Multi-record operation:

insert into t(a,b,c) values ('a1','b1','c1'),('a2','b2','c2')
on duplicate key update t.c=values(t.c)