【SQL】環境PostgreSQL8.2

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

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

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

１．表定義例

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

２．トリガで使用するストアドファンクション（＝プロシジャ）の定義例

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で呼ばれた場合も、メーッセージを表示する処理を入れています。

３．トリガの定義例

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

４．テストデータの格納例

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);

５．更新のテスト

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

６．結果の確認

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

７．留意事項

(1)トリガの削除例

drop trigger tri_test2 on test2_tbl cascade;

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

drop table test2_tbl cascade;

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

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