人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

【SQL】環境PostgreSQL8.2

test1_tbl

| id | num |
|----+-----|
| aa | 100 |
| bb | 200 |
| cc | 300 |

test2_tbl

| id | time |
|----+------|
| aa | 2000 |
| bb | 1000 |
| cc | 5000 |

test2_tblのテーブルにidがbbでかつtimeが3000以下のレコードがあった場合のみ以下のUPDATEをする。
test2_tbl該当レコードのtimeを0にする、同時にtest1_tblテーブルのidがbbのレコードのnumを+1する。

結果以下のようにしたいのですが、これをクエリ1文で実現できないでしょうか?

test1_tbl

| id | num |
|----+-----|
| aa | 100 |
| bb | 201 |
| cc | 300 |

test2_tbl

| id | time |
|----+------|
| aa | 2000 |
| bb | 0 |
| cc | 5000 |

宜しくお願いします。

●質問者: tenshiks
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:AA BB CC SQL TIME
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● chuken_kenkou
●40ポイント

UPDATEの1文で、「複数表を更新」する機能を持っているRDBMSも存在しますが、少数派だと思います。

PostgreSQLでは、UPDATEの1文での複数表の更新は、実装していません。

そのため、トリガ等で実装する必要があります。


1.表定義例

create table test1_tbl
(id char(2) primary key,
 num int);
create table test2_tbl
(id char(2) primary key,
 "time" dec(4));

2.トリガで使用するストアドファンクション(=プロシジャ)の定義例

create or replace function upd_test2()
 returns trigger as $$
 declare
 rcnt int;
 begin
 raise info 'trigger started,op=%',tg_op;
 if tg_op='UPDATE' then -- updateで呼ばれた場合
 raise info 'update old id=%,time=%',old.id,old."time";
 raise info 'update new id=%,time=%',new.id,new."time";
 update test1_tbl
 set num=num+1
 where id=old.id;
 elseif tg_op='DELETE' then -- deleteで呼ばれた場合
 raise info 'delete old id=%',old.id;
 elseif tg_op='INSERT' then -- insertで呼ばれた場合
 raise info 'insert new id=%',new.id;
 else
 raise info 'invalid tg_op,tg_op=%',tg_op;
 end if;
 raise info 'trigger ended,op=%',tg_op;
 return new;
 end;
$$ language 'plpgsql'
;

※トリガの定義で、UPDATEでしか呼ばれないようにしますが、一応、DELETEとINSERTで呼ばれた場合も、メーッセージを表示する処理を入れています。


3.トリガの定義例

create trigger tri_test2
 after update on test2_tbl
 for each row execute procedure upd_test2();

4.テストデータの格納例

truncate table test1_tbl;
truncate table test2_tbl;

insert into test1_tbl values('aa',100);
insert into test1_tbl values('bb',200);
insert into test1_tbl values('cc',300);

insert into test2_tbl values('aa',2000);
insert into test2_tbl values('bb',1000);
insert into test2_tbl values('cc',5000);

5.更新のテスト

update test2_tbl
 set "time"=0
 where id='bb' and "time"<=3000;

6.結果の確認

select * from test1_tbl as x,test2_tbl as y
 where x.id=y.id
;

7.留意事項

(1)トリガの削除例

drop trigger tri_test2 on test2_tbl cascade;

(2)トリガの定義がある表定義の削除例

drop table test2_tbl cascade;


2 ● Mook
●30ポイント

一回のクエリでというのは無理だと思いますので、プロシージャを使ってはどうでしょうか。

(SQLだけというご要望でしたらすみません。)

CREATE OR REPLACE FUNCTION my_update( lid varchar, limit_time integer ) RETURNS integer AS $$
DECLARE
 cid varchar;
 up_count integer; 
BEGIN
 up_count := 0;
 FOR cid IN SELECT id FROM test2_tbl WHERE time < limit_time AND lid = id LOOP
 UPDATE test1_tbl SET num = num + 1 WHERE cid = id;
 UPDATE test2_tbl SET time = 0 WHERE cid = id;
 up_count := up_count + 1;
 END LOOP;
 RETURN up_count;
END;
$$ LANGUAGE plpgsql;

のプロシージャを作成し、


SELECT my_update( 'bb', 3000 );

を実行すれば、期待の動作になると思います。

http://kaiunix.cs.shinshu-u.ac.jp/Lesson/DataBase/POSTGRE/sql-cr...

◎質問者からの返答

chuken_kenkouさん、Mookさん、回答ありがとう御座いました。

丁寧な解説ありがとうございます。

1回のクエリではおそらく無理だと思っていましたので満足です。

またよろしくお願いします。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ