How to use ON DUPLICATE KEY for UPDATE Consider a table of 

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


Updating the value of number as

UPDATE test SET number=number + 1 WHERE external_id='X'


or

UPDATE test SET number='Y' WHERE external_id='X'


Since external_id-number couple is unique, it will not update if the new value of number exists.

How can I use something like INSERT ... ON DUPLICATE KEY to increase the value of number by number + 1 to reach an available value according to the UNIQUE Constraint and complete the UPDATE task?
First a sample table

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

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

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

mysql&gt;


Let's insert an initial row and SELECT it back

mysql&gt; INSERT INTO test (external_id,number,value)
    -&gt; VALUES (200,15,'Y')
    -&gt; 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&gt;


Let's insert the same thing again

mysql&gt; INSERT INTO test (external_id,number,value)
    -&gt; VALUES (200,15,'Y')
    -&gt; 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&gt;


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

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

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

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

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

mysql&gt;


This time change value and number

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

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

mysql&gt; 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)

mysql&gt;


Hope this helps.

Give it a Try !!!