uniqueキーをupdateで+1するとERROR 1062: Duplicate entry になる

質問

create table table1 (
a tinyint unsigned not null,
b tinyint unsigned not null,
unique index_table1 ( a, b )
);
insert into table1 values('1','1');
insert into table1 values('1','2');

update table1 set b = b+1 where a = '1';

とすると、
ERROR 1062: Duplicate entry '1-2' for key 1
とエラーになってしまいます。
どうやら unique の制約に引っかかっているみたいです。
結果としては
1,2
1,3
になればいいのですが、一個一個bが多い順番にSQLを発行するか、unique 制約を外すしかないんでしょうか？
（面倒なので、一発でやりたいのです）

回答No.2

yambejp · ベストアンサー

そもそもが、unique属性をつけているフィールドを
インクリメントするという運用方法がまちがっているのでは？
unique値は入力時に仕様が確定していて当然ですので
あとから変更して値がかぶる可能性があるような作り方は
するべきではありません。

もし非常にイレギュラーな処理として、今回の作業が発生したという
のであれば、ご自身でも指摘されている通り、uniqueをはずして
変更してからuniqueをつけなおすというやり方が妥当でしょう。

お礼

回答ありがとうございます。
やはり仕様を考え直したほうがいいのでしょうか。

今回のことは、ユーザーにテーブルをいじらせるためのものです。

<table>
<tr><td>1-1</td><td>1-2</td></tr>
<tr><td>2-1</td><td>2-2</td></tr>
</table>
とあり、1-1には「あ」という文字があるとします。
ユーザーには一列目などにセルを入れることができるようにすることを目的にしています。
<table>
<tr><td>1-1</td><td>1-2</td></tr>
<tr><td>x-1</td><td>x-2</td></tr>
<tr><td>2-1</td><td>2-2</td></tr>
</table>
このような挿入を許可するために、元の2-1、2-2を3-1、3-2として値を変更しようとして質問のようなものになりました。
このため、同じ値に書き込ませないためにunique属性をつけています。
多分仕様的に難しいのかもしれませんね。

とりあえず、移動するものを一つ一つ指定するような形では動くようにしましたが、
他に良いやり方がありましたら教えてください。
よろしくお願いします。

回答No.3

hirosa-ok

MySQL は UPDATE でも ORDER BY 句が使用できます。
バージョン3.23.38のドキュメントにも記述されているので、質問者さんの環境でも使用できるのでは。

お礼

回答ありがとうございます。

update table1 set b = b+1 where a = '1' order by b desc;

として試しましたが、
ERROR 1064: You have an error in your SQL syntax near 'order by `b` desc' at line 1
とエラーが出てしまいました。

できるかと思ったのに残念です。

回答No.1

chukenkenkou

MySQLのバージョンは何ですか？
MySQL 5.0移行なら、ストアドプロシジャを活用できます。

補足

すいません。バージョンを書くのを忘れてました。
3.23.58ですので、ストアドプロシージャは使用できません。