データの入れ替えを同時に行ないたいのです

質問

よろしくおねがいします。

このようなページを見たのですが、
イマイチ理解が出来ませんでした。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=418316
（特に-の演算子が調べられませんでした）

二つのデータ（カラム同士）を入れ替えるのに、
二つとも一旦違う値にして、
元のデータに戻すというやり方で何とか対応しているのですが、もう少し効率の良い書き方、関数等ご存知の方いらっしゃいませんでしょうか？
参考ページの解説でも違ったやり方でも結構です。

どうかご教授願います。

回答No.1

chukenkenkou · ベストアンサー

>特に-の演算子が調べられませんでした

普通に、負数や減算ですよ？

>Update テーブル名 set No = 5 - No where No in ('2', '3');

上記例では、文字なのか数値なのか、必ずしも正しい使い方ではありませんが。。。
文字のように記述（'で囲む）しても演算できると仮定して説明します。

Noの値が2か3なら検索して、5-Noするのですから、
No=2なら3、
No=3なら2
という結果が得られます。
つまりこの質問で示されていた、2←→3の入れ替えに特化した方法です。

>もう少し効率の良い書き方、関数等ご存知の方

具体的なことを聞きたいなら、具体的な例を示して質問しましょう。

お礼

早速のご回答ありがとうございます。
>5-Noするのですから、
なるほど、先入観で理解出来ていませんでした。
納得です。

>具体的な例を示して質問しましょう。
失礼致しました。

要求なのですが、
シーケンシャルに振ってあるIDを入れ替えたいのです。
行として持っているデータは変更せずに
一意に振られている連番
（商品IDのように行のデータに対して一意に振られている連番）
を入れ替えたいのです。連番はPKです。
はっきり言ってテーブルの設計ミスなのですが、
ひとまず運用上必要なので教えを乞いています。

現在は
ID=1とID=2を入れ替える場合、
変数の入れ替え要領で、
ID=1をID=3へ変更、ID=2をID=4へ変更
ID=3をID=2に変更、ID=4をID=1へ変更
といった具合にUPDATEして対応しています。

ヒューマンエラーが起こりやすそうなので、
何か良い方法は無いかと思いまして・・・
よろしくお願い致します。

回答No.5

chukenkenkou

#1、#4回答者です。

#4回答について、補足説明をしておきます。

(1)select into～
　標準SQLとは、構文が異なっています。
　→intoの位置が違う。
(2)select into～でのfor update指定
　更新前提であることを示し、多トランザクションからの同時使用を抑止
(3)updateでエラーの場合
　関数が例外発生でエラーとなるので、関数内ではチェックしていません。
(4)例示の関数を、「プライマリキー更新（入れ替え）」の方式で流用する場合
update列を単純にプライマリキー更新（例示のケースではc1列）に変えると、二つ目のupdateで重複エラーになります。
従来通り、一旦、存在し得ない値に更新してください。
→入れ替える両方の値を、存在し得ない値に更新する必要はありません。
どちらか一方を、存在し得ない値に一時的に更新してください。
例示すると、下記のようになります。

create or replace function functest1(x1 int,x2 int)
returns int as $$
-- ------------------------
-- 二つの行の値の入れ替え（キー更新）
-- ------------------------
declare
begin
update t1 set c1=-x1 where c1=x1;
update t1 set c1=x1 where c1=x2;
update t1 set c1=x2 where c1=-x1;
return 0;
end;
$$ language 'plpgsql'
;

お礼

度々のご回答まことにありがとうございます。

皆様のご回答を見てもやはりテーブル設計が
非常に悪いとしか言いようがありませんね・・・

悪い見本として今後に活かしたいと思います。

ありがとうございました。

補足

沢山のご回答真にありがとうございます。
質問を締め切らせて頂きます。

非常に勉強になりました。

全ての回答にポイントを発行したいのですが、
システムの関係上そうはいかない事をお許しください。

ありがとうございました。

回答No.4

chukenkenkou

#1回答者です。

>ヒューマンエラーが起こりやすそうなので

そういう点では、ストアド・プロシジャ等を利用して、発行SQL自体はブラックボックスにする方法がいいかと思います。
「この処理の場合は、このストアド・プロシジャを実行」とし、APIのみプログラマに公開するという方式です。

「設計ミス」とのことですが、プライマリキーを何度も更新すると、インデクス更新のオーバヘッドもあると思うので、発想を変えて「プライマリキー以外の列を入れ替える」というのはどうでしょうか？
列数や列長により、効果があるかどうかは変わってくるとも思いますが。。。

以下に、プライマリキーは更新せず、それ以外のデータを入れ替える方法でのPL/pgSQL例を示します。
これを、プライマリキー更新のパターンに変更するのは容易だと思いますので、参考にしてください。

(1)テスト用の表定義＆データ
create table t1
(c1 int primary key,
c2 char(1),
c3 dec(15,5),
c4 varchar(10));

insert into t1 values(1,'a',100.5,'a');
insert into t1 values(2,'b',200.5,'b');
insert into t1 values(3,'c',300.5,'c');
insert into t1 values(4,'d',400.5,'d');
insert into t1 values(5,'e',500.5,'e');

(2)関数定義
-- drop function functest1(x1 int,x2 int);
create or replace function functest1(x1 int,x2 int)
returns int as $$
declare
rec1 record;
rec2 record;
begin
select into rec1 * from t1
where c1=x1
for update;
if not found then
return 4;
end if;
select into rec2 * from t1
where c1=x2
for update;
if not found then
return 8;
end if;
update t1
set c2=rec2.c2,
c3=rec2.c3,
c4=rec2.c4
where c1=x1;
update t1
set c2=rec1.c2,
c3=rec1.c3,
c4=rec1.c4
where c1=x2;
return 0;
end;
$$ language 'plpgsql'
;

(3)実行
select functest1(1,2);

お礼

お礼が遅くなってしまい申し訳ありません。

詳細且つ、丁寧なご回答ありがとうございます。

>発想を変えて「プライマリキー以外の列を入れ替える」というのはどうでしょうか？

勉強になります！
ありがとうございます。

関数化も試してみましたが、
発想の転換に驚きました。

貴重なご回答ありがとうございました。

回答No.3

kazuho_goo

こんなのはどうでしょう？
update 商品テーブル
set 商品ID = CASE WHEN 商品ID = 1 THEN 2
WHEN 商品ID = 2 THEN 1
ELSE 商品ID
END
注意)
・テスト環境がないため、テストしていません。
・バージョンによってはCASE文が使用できない可能性あり。

お礼

お礼が遅くなってしまい申し訳ありません。

なるほど、思いつきませんでした！

上記例を試して見ましたが残念ながらCASEは
使えませんでした・・・

貴重なご回答ありがとうございました。

回答No.2

tany180sx

処理自体はプロシージャでもシェルでもPHPでも
好きなものを使えばいいと思います。

入れ替え関数(ID1, ID2) {
TEMP1 = ID1
TEMP2 = ID2
ID1 = 0 or MAX
ID2 = TEMP1
ID1 = TEMP2
}

お礼

お礼が遅くなってしまい申し訳ありません。

上記関数を定義して試してみました。
やはり簡単なアップデート文でというのは難しそうですね。

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