[PostgreSQL]更新順序を指定するUPDATE処理について

Question

Postgresql での質問です。
以下の変更情報TBLをもとにお客様TBLを更新する処理を行いたいと考えています。
変更情報テーブルは必ずしも変更日の順序で入っているわけではないため、
更新の順序を指定できなければ、変更後にID1001,連番1,カード番号1001001
とならなくなる可能性があります。

記載のSQLのままだと更新順序(ORDER BYのような)指定が出来ていないため、2018/08/01のデータで更新がされてしまう可能性があるのではと思っています。
MySQLだとUPDATEにORDER BYが使えるとの記述があったのですが、PostgreSQLのため難航中です。

SQLのサンプルや実装方法の提案等頂けたら助かります。

変更情報TBL

ID	連番	カード番号	旧ID	旧連番	旧カード番号	変更日
1001	1	1001003	1001	2	10010003	2018/08/20 12:00:00
1001	2	1001003	0001	3	00010001	2018/08/01 11:00:00
1005	5	1001005	0005	4	00010010	2018/08/20 13:00:00

修正)
2列目旧連番3→連番2　⇒　1列目旧連番2(実態は2列目の連番2)→連番1
変更日で見た場合、3→2 2→1　の様になるため、更新順序の指定が出来るのであれば、
変更日で順序を指定し、お客様TBLの2列目が「1001」「1」「1001003」
と出来ないかと考えています。
この場合は変更日が最も新しいモノという条件では追えなくなるため、
全レコードを順に更新しなければなりません。

お客様TBL

ID	連番	カード番号	性別	名前
0001	1	0001001	M	太郎
0001	3	0001001	M	太郎
0005	4	0001010	F	花子

UPDATE customer SET
    customer.ID         = changes.ID
    ,customer.連番      = changes.連番
    ,customer.カード番号 = changes.カード番号
FROM お客様TBL AS customer
INNER JOIN 変更情報TBL AS changes ON
    customer.カード番号 = changes.旧カード番号 AND
    customer.連番      = changes.旧連番;

Answer 1

変更情報テーブルが変更対象のキーの変遷であり、全ての変遷の初めと終わりを見たときに整合性が担保（キー重複が発生しない）という条件なら、WITH RECURSIVEを使用すれば開始から終端までを１行に纏めることができるので、それを元に更新すれば良いかと思います。

Answer 2

変更情報TBLの結合条件orWHERE句で、「旧連番、旧カード番号が同じデータの中で最大の変更日」のデータを指定する条件を追加すると、旧連番が3旧カード番号が00010001のデータも変更日2018/08/20のデータで更新することができます。

INNER JOIN 変更情報TBL AS changes ON
    customer.カード番号 = changes.旧カード番号 AND
    customer.連番      = changes.旧連番
    changes.変更日     = (「旧連番、旧カード番号が同じデータの中で最大の変更日」を取得するサブクエリ);

サブクエリの書き方は色々とあると思うので、ご自身で考えてみて下さい。

---

↓↓↓追記↓↓↓
sazi様の回答にあるようにWITH RECURSIVEを使用する方法で解決できそうです。

WITH RECURSIVE OKYAKUSAMA AS (
 SELECT '0001' AS ID, 1 AS RENNBANN, '0001001' AS CARDNO
 UNION 
 SELECT '0001' AS ID, 3 AS RENNBANN, '0001001' AS CARDNO
 UNION 
 SELECT '0005' AS ID, 4 AS RENNBANN, '0001010' AS CARDNO
)
, HENNKOU AS (
 SELECT '1001' AS ID, 1 AS RENNBANN, '1001003' AS CARDNO, '1001' AS KYUUID, 2 AS KYUURENNBANN, '1001003' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/20 12:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
 UNION
 SELECT '1001' AS ID, 2 AS RENNBANN, '1001003' AS CARDNO, '0001' AS KYUUID, 3 AS KYUURENNBANN, '0001001' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/01 11:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
 UNION
 SELECT '1005' AS ID, 5 AS RENNBANN, '1001005' AS CARDNO, '0005' AS KYUUID, 4 AS KYUURENNBANN, '0001010' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/20 13:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
)
, TAISYO(ID, RENNBANN, CARDNO, MOTOID, MOTORENNBANN, MOTOCARDNO, DEPTH) AS (
 SELECT CHANGES.ID, CHANGES.RENNBANN, CHANGES.CARDNO, CUSTOMER.ID AS MOTOID, CUSTOMER.RENNBANN AS MOTORENNBANN, CUSTOMER.CARDNO AS MOTOCARDNO, 1 AS DEPTH
 FROM OKYAKUSAMA AS CUSTOMER
 INNER JOIN HENNKOU AS CHANGES
  ON CUSTOMER.ID = CHANGES.KYUUID
  AND CUSTOMER.RENNBANN = CHANGES.KYUURENNBANN
  AND CUSTOMER.CARDNO = CHANGES.KYUUCARDNO
 UNION ALL
 SELECT CHANGES.ID, CHANGES.RENNBANN, CHANGES.CARDNO, TAISYO.MOTOID, TAISYO.MOTORENNBANN, TAISYO.MOTOCARDNO, DEPTH + 1
 FROM HENNKOU AS CHANGES, TAISYO
 WHERE TAISYO.ID = CHANGES.KYUUID
  AND TAISYO.RENNBANN = CHANGES.KYUURENNBANN
  AND TAISYO.CARDNO = CHANGES.KYUUCARDNO
)
SELECT SUB_TAISYO.ID, SUB_TAISYO.RENNBANN, SUB_TAISYO.CARDNO 
FROM OKYAKUSAMA, (
  SELECT SUB.*
  FROM (SELECT TAISYO.*, MAX(TAISYO.DEPTH) OVER(PARTITION BY TAISYO.MOTOID, TAISYO.MOTORENNBANN, TAISYO.MOTOCARDNO) MAX_DEPTH FROM TAISYO) SUB
  WHERE SUB.DEPTH = SUB.MAX_DEPTH
 ) SUB_TAISYO
WHERE OKYAKUSAMA.ID = SUB_TAISYO.MOTOID
 AND OKYAKUSAMA.RENNBANN = SUB_TAISYO.MOTORENNBANN
 AND OKYAKUSAMA.CARDNO = SUB_TAISYO.MOTOCARDNO

WITH句のOKYAKUSAMAとHENNKOUはお客様TBLと変更情報TBLをインラインで表現しているだけなので読み飛ばして下さい。（当方の環境にテーブル作成するのが面倒であったので了承下さい。。）
TAISYOの部分がWITH RECURSIVEの構文を使用している部分です。
まず、非再帰的表現部分でお客様TBLのキーと変更情報TBLの旧キーが同じデータを抽出しています。ここで、お客様情報のキーを元キー(MOTOID, MOTORENNBANN, MOTOCARDNO)として保持しています。さらに、深さを表すDEPTHを1として定義しています。
再帰的表現部分では、非再帰的表現部分で取得できた結果の変更情報TBLのキーが変更情報TBLの旧キーと同じデータを抽出しており、これを繰り返します。繰り返すたびDEPTHは加算されます。
その結果は以下のようになります。

ID;RENNBANN;CARDNO;MOTOID;MOTORENNBANN;MOTOCARDNO;DEPTH
"1001";2;"1001003";"0001";3;"0001001";1
"1001";1;"1001003";"0001";3;"0001001";2
"1005";5;"1001005";"0005";4;"0001010";1

WITH以降のSELECT句でこの結果からお客様TBLのキーと元キーが同じ、かつ、深さが最大のデータを取得すると更新対象とその更新値が取得できます。

他にもやり方はあるかと思いますが、私のつたないSQLではこれが限界でした。。。
わかりづらい部分が多いと思いますし、やり方はこれだけではないはずなので、ご自身に合った方法を見つけて課題解決下さい。

Comments

[mikan_s4n]

回答頂いてからで非常に申し訳ないのですが、変更情報TBLのデータに認識違いがあり、修正致しました。
丁寧に書いていただき、ありがとうございます。

[mikan_s4n]

sazi様の回答から理解しやすいように例を作って下さりありがとうございます。
再帰的処理はなかなかイメージが掴めなかったのでとても助かりました。
sazi様とどちらをBAにするかは迷いましたが、よりイメージのしやすいこちらをBAにさせてもらいます。
ありがとうございました。

[SE-studying-now]

解決してよかったです。
BAに選んで頂きありがとうございます！
私も勉強になった次第です！お互い頑張りましょう！

Answer 3

一括で update するのではなく、変更情報TBL を逐次処理しなければならない、のでは？

マニュアルの V サーバプログラミング の、SQL 手続き言語とかカーソルとかをご確認ください。

Comments

[mikan_s4n]

仰る通り、変更情報テーブルを１レコードずつ逐次処理していくのが確実だとは思います。
単一のSQLで更新順序を指定すれば逐次処理のようなことが出来ないかと思ったのですが、指摘のように手続き言語等で制御するのがよいのですかね……。

Answer 4

基本的には「余計な情報は省いたデータでupdateする」ことになるかと。
つまり「旧連番ごとに最終変更日を持つ行を求める」という作業を先に行います。
やり方は結構いろいろあります。

・「ウィンドウ関数」を使う
・「join lateral」を使う
stackoverflow

他には未検証ですけど

「同じ旧連番同士で自分の変更日よりあとの変更日を持たない」データを検索

select *
from 変更情報TBL as A
where not exists(
  select 0
  from 変更情報TBL as B
  where B.旧連番=A.旧連番
    and B.変更日>A.変更日
)

あとは「同じ旧連番同士で最大の変更日と同じ変更日」のデータを検索などすればいいかと。

あとはこれらを元にしてupdateすればいいです。重複データさえ消えてしまえばこっちのもんです。