Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you want to update all rows that have external_id='X' by increasing the number by 1? Or just one row is to be updated? –  ypercube Sep 19 '13 at 7:20
add comment

1 Answer

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)

mysql>

Hope this helps.

Give it a Try !!!

share|improve this answer
    
I really like your way of answering and I deeply appreciate it. However, this is the first method came into my mind. 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. –  All Mar 27 '12 at 16:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.