質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.42%
Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

728閲覧

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

aayaka

総合スコア1

Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2023/05/23 02:56

編集2023/05/23 08:43

実現したいこと

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

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

前提

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

実績履歴テーブル(一部抜粋)

契約NO契約年月日取消年月日金額登録年月日登録時間明細NO
A12023/02/15NULL3,0002023/02/151700001
A12023/02/15NULL3,0002023/04/051700001
A12023/02/15NULL3,0002023/04/051800001
A12023/03/15NULL3,0002023/03/151700002
A12023/03/15NULL3,0002023/04/051700002
CREATE TABLE 実績履歴 ( 契約NO CHAR(20), 契約年月日 DATE, 取消年月日 DATE, 金額 NUMBER(11,0), 登録年月日 DATE, 登録時間 VARCHAR2(7), 明細NO 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
A12023/02/152023/04/053,0001
A12023/02/152023/04/053,0001
A12023/03/152023/04/053,0002
CREATE TABLE 取消履歴 ( 契約NO CHAR(20), 契約年月日 DATE, 取消年月日 DATE, 金額 NUMBER(11,0), 明細NO 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の指定がおかしいと思われますがどう修正すればいいかわからず詰まってしまっています。

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

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

該当のソースコード

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

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

実行結果

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

契約NO契約年月日取消年月日金額登録年月日登録時間明細NO
A12023/02/152023/04/053,0002023/02/151700001
A12023/02/15NULL3,0002023/04/051700001
A12023/02/152023/04/053,0002023/04/051800001
A12023/03/15NULL3,0002023/03/151700002
A12023/03/152023/04/053,0002023/04/051700002

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

契約NO契約年月日取消年月日金額登録年月日登録時間明細NO
A12023/02/152023/04/053,0002023/02/151700001
A12023/02/152023/04/053,0002023/04/051700001
A12023/02/15NULL3,0002023/04/051800001
A12023/03/152023/04/053,0002023/03/151700002
A12023/03/15NULL3,0002023/04/051700002

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

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

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

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


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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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
aayaka

2023/05/23 06:10

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

2023/05/23 09:38

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

回答2

0

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

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

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

SQL

1begin 2 for rec in ( 3 select 4 a.rowid 5 ,ROW_NUMBER() OVER( 6 PARTITION BY a.契約NO, a.契約年月日, a.金額 7 ORDER BY a.登録年月日, a.登録時間, a.明細NO ) AS total_count 8 ,(select count(1) from 取消履歴 b 9 where b.契約NO = a.契約NO 10 and b.契約年月日 = a.契約年月日 11 and b.金額 = a.金額) update_count 12 ,(select max(取消年月日) from 取消履歴 c 13 where c.契約NO = a.契約NO 14 and c.契約年月日 = a.契約年月日 15 and c.金額 = a.金額) cancel_date 16 from 実績履歴 a 17 where exists( 18 select 1 from 取消履歴 b 19 where b.契約NO = a.契約NO 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

総合スコア26

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

aayaka

2023/05/24 02:17

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

0

ベストアンサー

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

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

SQL

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

2023-05-23 20:00追記

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

SQL

1SELECT AAA.* 2FROM ( 3SELECT 実績.* ,ROW_NUMBER() OVER(PARTITION BY 実績.契約NO, 実績.契約年月日, 実績.金額 4 ORDER BY 実績.登録年月日 ASC, 実績.登録時間 ASC, 実績.明細NO ASC ) AS 順番 5 FROM 実績履歴 実績 6 WHERE 実績.契約NO = REC.契約NO 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 取消.契約NO = 実績.契約NO 7 AND 取消.契約年月日 = 実績.契約年月日 8 AND 取消.金額 = 実績.金額 9)

投稿2023/05/23 08:35

編集2023/05/23 11:05
neko_the_shadow

総合スコア2276

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

aayaka

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 実績.明細NO = REC.明細NO」は不要ですね。 ・OVER句に「PARTITION BY 実績.契約NO, 実績.契約年月日, 実績.金額」を追加する必要がありそうです。 ・「FOR REC IN」はJOINではなくEXISTSのほうがよさそうです
neko_the_shadow

2023/05/23 10:55

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

2023/05/24 02:21

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.42%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問