【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 |

宜しくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/11/20 08:04:44
  • 終了:2007/11/27 01:31:45

回答(2件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/20 10:13:16

ポイント40pt

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;

id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912007/11/20 12:50:59

ポイント30pt

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

(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...

id:tenshiks

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

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

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

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

2007/11/26 17:14:16

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません