How to use ON DUPLICATE KEY for UPDATE

First a sample table

mysql> drop database if exists ali;
Query OK, 1 row affected (0.10 sec)

mysql> create database ali;
Query OK, 1 row affected (0.00 sec)

mysql> use ali;
Database changed
mysql> CREATE TABLE test
    -> (
    -> id int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> external_id int(11),
    -> number smallint(5),
    -> value varchar(255),
    -> UNIQUE INDEX (external_id, number),
    -> PRIMARY KEY(id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql>

Let's insert an initial row and SELECT it back

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 1 row affected (0.06 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     15 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

Let's insert the same thing again

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 2 rows affected (0.10 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

OK let's try the value column at the same time

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     15 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 2 rows affected (0.06 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql>

This time change value and number

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'Z')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     15 | Z     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'A')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     16 | A     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

The problem is that UPDATE KEY is not recursive. In you last INSERT if you already have number 16, it will not change 15 to 17 (next available value). It will only try 16, and will fail due to duplicate key. 

id   external_id         number         value
1   200    16    Y
3   201    16    X
5   202    16    Z
6   202    15    Z

INSERT INTO test (external_id,number,value)
    VALUES (202,15,'Z')
     ON DUPLICATE KEY UPDATE
    number = number + 1,
     value = VALUES(value);

Error:
#1062 - Duplicate entry '202-16' for key 'external_id'

USE INSERT IGNORE KEYWORD 

ERROR WILL NOT COME AND RECORD WILL BE SKIPPED TO INERT

INSERT IGNORE INTO test (external_id,number,value)
    VALUES (202,15,'Z')
     ON DUPLICATE KEY UPDATE
    number = number + 1,
     value = VALUES(value);

To use INSERT ON DUPLICATE UPDATE syntax you must have a unique index indicating the columns you want to prevent duplicates in. So if you have say five specific columns that can't have duplicate values then you create your unique index on those five columns UNIQUE(col3,col6,col22,col34,col45) for example. So you could have duplicate values within each of those columns or three of five for example, but all five of those values can't be duplicates of another row with those five values already existing. At that point it would update instead of insert.