2つのテーブルからデータを抽出し、値を更新するSQLを作成したい

実現したいこと

ある契約情報を保管している実績履歴テーブルと取消履歴テーブルから
取消履歴テーブルの取消年月日を実績履歴テーブルの消込年月日に埋め込む
SQLを作成したいです。

お力添えいただけますと幸いです。

前提

１. 契約NO、契約年月日、金額が完全一致しているデータを抽出。
２. 完全一致の行が複数ある場合、「登録年月日」「登録時間」「明細NO」が小さいものから順に取消年月日を更新。
３. 実績履歴テーブルの取消年月日を取消履歴テーブルの取消年月日の値に更新。

実績履歴テーブル（一部抜粋）

契約NO	契約年月日	取消年月日	金額	登録年月日	登録時間	明細NO
A1	2023/02/15	NULL	3,000	2023/02/15	170000	1
A1	2023/02/15	NULL	3,000	2023/04/05	170000	1
A1	2023/02/15	NULL	3,000	2023/04/05	180000	1
A1	2023/03/15	NULL	3,000	2023/03/15	170000	2
A1	2023/03/15	NULL	3,000	2023/04/05	170000	2

CREATE TABLE 実績履歴
(
    契約ＮＯ                       CHAR(20),
    契約年月日                     DATE,
    取消年月日                     DATE,
    金額                           NUMBER(11,0),
    登録年月日                     DATE,
    登録時間                       VARCHAR2(7),
    明細ＮＯ                       NUMBER(3,0)
)
/
INSERT INTO 実績履歴
VALUES ('A1','2023/02/15',NULL,'3000','2023/02/15','170000','1')
/
INSERT INTO 実績履歴
VALUES ('A1','2023/02/15',NULL,'3000','2023/04/05','170000','1')
/
INSERT INTO 実績履歴
VALUES ('A1','2023/02/15',NULL,'3000','2023/04/05','180000','1')
/
INSERT INTO 実績履歴
VALUES ('A1','2023/03/15',NULL,'3000','2023/03/15','170000','2')
/
INSERT INTO 実績履歴
VALUES ('A1','2023/03/15',NULL,'3000','2023/04/05','170000','2')
/

消込履歴テーブル（一部抜粋）

契約NO	契約年月日	取消年月日	金額	明細NO
A1	2023/02/15	2023/04/05	3,000	1
A1	2023/02/15	2023/04/05	3,000	1
A1	2023/03/15	2023/04/05	3,000	2

CREATE TABLE 取消履歴
(
    契約ＮＯ                       CHAR(20),
    契約年月日                     DATE,
    取消年月日                     DATE,
    金額                          NUMBER(11,0),
    明細ＮＯ                       NUMBER(3,0)
)
/
INSERT INTO 取消履歴 
VALUES ('A1','2023/02/15','2023/04/05','3000','1')
/
INSERT INTO 取消履歴 
VALUES ('A1','2023/02/15','2023/04/05','3000','1')
/
INSERT INTO 取消履歴 
VALUES ('A1','2023/03/15','2023/04/05','3000','2')
/

発生している問題

複数行あった場合に、更新対象の実績履歴テーブルが取得できず
間違った履歴に取消年月日が更新されてしまっている状態です。

FORでSELECTを回して更新データを抽出しているのですが
W_CNTの指定がおかしいと思われますがどう修正すればいいかわからず詰まってしまっています。

２. 完全一致の行が複数ある場合、「登録年月日」「登録時間」「明細NO」が小さいものから順に取消年月日を更新。

小さいものが取得できてません。

該当のソースコード

Object BrowserのPROCEDUREで以下処理を実行。

    -- 取消情報抽出
    FOR REC IN (
            SELECT DISTINCT
                   取消.*
            FROM 取消履歴 取消
                ,実績履歴 実績 
            WHERE
                  取消.契約ＮＯ = 実績.契約ＮＯ
              AND
                  取消.契約年月日 = 実績.契約年月日
              AND 
                  取消.金額 = 実績.金額
    )
    LOOP
    
        W_CNT := W_CNT + 1;
　　　-- 更新対象の実績履歴を抽出
        FOR REC2 IN (
            SELECT AAA.*
              FROM
                ( 
                 SELECT 実績.*
                       ,ROW_NUMBER() OVER(ORDER BY 実績.登録年月日 ASC, 実績.登録時間 ASC, 実績.明細ＮＯ ASC ) AS 順番
                 FROM 実績履歴 実績
                 WHERE
                       実績.契約ＮＯ   = REC.契約ＮＯ
                   AND
                       実績.契約年月日 = REC.契約年月日
                   AND 
                       実績.金額   = REC.金額
                   AND
                       実績.明細ＮＯ = REC.明細ＮＯ
                )
                AAA
            WHERE 順番 = W_CNT
        )
        LOOP

          -- 更新処理
          UPDATE
                実績履歴 UPD実績
          SET
                UPD実績.取消年月日 = REC.取消年月日
          WHERE
                UPD実績.契約ＮＯ   = REC2.契約ＮＯ
            AND
                UPD実績.契約年月日 = REC2.契約年月日
            AND 
                UPD実績.金額 = REC2.金額
            AND 
                UPD実績.登録年月日 = REC2.登録年月日
            AND
                UPD実績.登録時間 = REC2.登録時間
            AND
                UPD実績.明細ＮＯ = REC2.明細ＮＯ
            ;

        END LOOP;
    END LOOP;

実行結果

〇誤
実際に更新された情報

契約NO	契約年月日	取消年月日	金額	登録年月日	登録時間	明細NO
A1	2023/02/15	2023/04/05	3,000	2023/02/15	170000	1
A1	2023/02/15	NULL	3,000	2023/04/05	170000	1
A1	2023/02/15	2023/04/05	3,000	2023/04/05	180000	1
A1	2023/03/15	NULL	3,000	2023/03/15	170000	2
A1	2023/03/15	2023/04/05	3,000	2023/04/05	170000	2

〇正
本当はこのように更新されてほしい。

契約NO	契約年月日	取消年月日	金額	登録年月日	登録時間	明細NO
A1	2023/02/15	2023/04/05	3,000	2023/02/15	170000	1
A1	2023/02/15	2023/04/05	3,000	2023/04/05	170000	1
A1	2023/02/15	NULL	3,000	2023/04/05	180000	1
A1	2023/03/15	2023/04/05	3,000	2023/03/15	170000	2
A1	2023/03/15	NULL	3,000	2023/04/05	170000	2

調査したこと、試したこと

①W_CNT の指定方法をどうにかする必要あり。

②partition byを利用したら上手くいくかも？

ROW_NUMBER() OVER(ORDER BY 実績.登録年月日 ASC, 実績.登録時間 ASC, 実績.明細ＮＯ ASC ) AS 順番

良い方法ありますでしょうか？

ご教授のほどよろしくお願いいたします。

Orlofsky

2023/05/23 04:38

質問は修正できます。使っているテーブルのCREATE TABLE文、テータをINSERT文で数件、その希望するSELECT結果結果と実際のSELECT結果をMarkdownの[コード]で提示してください。 https://teratail.com/help/question-tips#questionTips37

logres_Fan

2023/05/23 06:01

消込履歴テーブル（一部抜粋）の2行目と3行目について、実際のテーブルではレコードの並び順が逆転している可能性はありますか？契約NO,契約年月日,取消年月日,金額,明細NO A1,2023/02/15,2023/04/05,3,000,1 A1,2023/03/15,2023/04/05,3,000,2 A1,2023/02/15,2023/04/05,3,000,1

a5i

2023/05/23 06:10

SELECT時に特に並び順を指定していないので可能性はあるかもしれません。

XiiTuzi

2023/05/23 09:38

消込履歴テーブルの一行目と二行目は同じデータではありませんか？提示された情報では、どちらのレコードの取消年月日で更新すればよいのか判断できません。

行動規範の内容に同意します

回答2件

消込履歴テーブルの取消年月日は、契約NO・契約年月日・金額で一意に決まるという前提で回答します。
※もし違う場合でも、暗黙カーソルはあある程度アレンジしやすいように作っているつもりです。

繰り返しますが、消込履歴テーブルの取消年月日については十分確認の上実行してください。

質問とは関係ありませんが、カーソルループで更新するのであれば、提示ソースのようにROWIDを使用する方が良いでしょう。
元ソースのように更新条件が複数あるとテストが大変です。
また、より厳密な制御が必要な場合は行ロックをかける必要があります。そのあたりは質問者さんで判断してください。

SQL
1begin
2  for rec in (
3    select
4       a.rowid
5      ,ROW_NUMBER() OVER(
6         PARTITION BY a.契約ＮＯ, a.契約年月日, a.金額
7         ORDER BY a.登録年月日, a.登録時間, a.明細ＮＯ ) AS total_count
8      ,(select count(1) from 取消履歴 b
9        where b.契約ＮＯ = a.契約ＮＯ
10          and b.契約年月日 = a.契約年月日
11          and b.金額 = a.金額) update_count
12      ,(select max(取消年月日) from 取消履歴 c
13        where c.契約ＮＯ = a.契約ＮＯ
14          and c.契約年月日 = a.契約年月日
15          and c.金額 = a.金額) cancel_date
16    from 実績履歴 a
17    where exists(
18      select 1 from 取消履歴 b
19      where b.契約ＮＯ = a.契約ＮＯ
20        and b.契約年月日 = a.契約年月日
21        and b.金額 = a.金額)
22  )
23  loop
24    if rec.total_count <= rec.update_count then
25      update 実績履歴
26      set 取消年月日 = rec.cancel_date
27      where rowid = rec.rowid;
28    end if;
29  end loop;
30end;

投稿2023/05/23 15:26

XiiTuzi

総合スコア68

a5i

2023/05/24 02:17

ご回答ありがとうございます。テストの観点からのご指摘大変勉強になります。 ROWIDを使用したソース参考にさせていただきます。

行動規範の内容に同意します

ベストアンサー

２. 完全一致の行が複数ある場合、「登録年月日」「登録時間」「明細NO」が小さいものから順に取消年月日を更新。

ここがおそらく問題で「取消年月日が未設定(NULL)のデータを更新する」という条件が抜けていると思われます。ソースコード上はFOR REC2 INのSQLを以下の通り修正すればよいかと思います。

SQL
1SELECT AAA.*
2FROM ( 
3SELECT 実績.* ,ROW_NUMBER() OVER(ORDER BY 実績.登録年月日 ASC, 実績.登録時間 ASC, 実績.明細ＮＯ ASC ) AS 順番
4  FROM 実績履歴 実績
5 WHERE 実績.契約ＮＯ   = REC.契約ＮＯ
6   AND 実績.契約年月日 = REC.契約年月日
7   AND 実績.金額       = REC.金額
8   AND 実績.明細ＮＯ   = REC.明細ＮＯ
9   AND 実績.取消年月日 IS NULL
10) AAA
11WHERE 順番 = 1

2023-05-23 20:00追記

あらためて見直しました。「AND 実績.明細ＮＯ = REC.明細ＮＯ」は不要ですね。あとOVER句にPARTITION BYの追記も必要そうです。

SQL
1SELECT AAA.*
2FROM ( 
3SELECT 実績.* ,ROW_NUMBER() OVER(PARTITION BY 実績.契約NO, 実績.契約年月日, 実績.金額 
4                                 ORDER BY 実績.登録年月日 ASC, 実績.登録時間 ASC, 実績.明細ＮＯ ASC ) AS 順番
5  FROM 実績履歴 実績
6 WHERE 実績.契約ＮＯ   = REC.契約ＮＯ
7   AND 実績.契約年月日 = REC.契約年月日
8   AND 実績.金額       = REC.金額
9   AND 実績.取消年月日 IS NULL
10) AAA
11WHERE 順番 = 1

また「取消情報抽出」の処理 (FOR RECの部分) はJOINではなくEXISTSで書いたほうがよさそうです。

SQL
1SELECT *
2FROM 取消履歴 取消
3WHERE EXISTS (
4  SELECT *
5   FROM 実績履歴 実績 
6  WHERE 取消.契約ＮＯ = 実績.契約ＮＯ
7    AND 取消.契約年月日 = 実績.契約年月日
8    AND 取消.金額 = 実績.金額
9)

投稿2023/05/23 08:35

編集2023/05/23 11:05

neko_the_shadow

総合スコア2374

a5i

2023/05/23 08:50

ご回答いただきありがとうございます！試してみたのですが更新されてほしい実行結果にはならずでした(;_;) 【実行結果】契約NO 契約年月日取消年月日金額登録年月日登録時間明細NO A1 2023/02/15 2023/04/05 3,000 2023/02/15 170000 1 A1 2023/02/15 NULL 3,000 2023/04/05 170000 1 A1 2023/02/15 NULL 3,000 2023/04/05 180000 1 A1 2023/03/15 NULL 3,000 2023/03/15 170000 2 A1 2023/03/15 2023/04/05 3,000 2023/04/05 170000 2

neko_the_shadow

2023/05/23 11:06 編集

改めて見直しました。・回答に記載したSQLですが「AND 実績.明細ＮＯ = REC.明細ＮＯ」は不要ですね。・OVER句に「PARTITION BY 実績.契約NO, 実績.契約年月日, 実績.金額」を追加する必要がありそうです。・「FOR REC IN」はJOINではなくEXISTSのほうがよさそうです

neko_the_shadow

2023/05/23 10:55

やりたいことの根本的なところを読み取れていない気もしますので、うまく動かないようでしたら、この回答は無視してください。

a5i

2023/05/24 02:21

追記いただいたSQLで更新されてほしい結果になりました。ありがとうございました！

行動規範の内容に同意します

あなたの回答