Home>

A question in Postgresql.
I would like to update the customer TBL based on the following change information TBL.
Because the change information table is not necessarily in the order of the change date,
If the update order cannot be specified, ID1001, serial number 1, card number 1001001 after the change It may not be.

I think that there is a possibility that it will be updated with the data of 2018/08/01 because the update order (like ORDER BY) can not be specified if it is SQL as described.
There was a description that ORDER BY can be used for UPDATE in MySQL, but it is difficult for PostgreSQL.

It would be helpful if you could provide SQL samples and suggestions for implementation methods.

Change information TBL

ID Serial number Card number Old ID Old serial number old card number Change date
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

Correction)
2nd row old serial number 3 → serial number 2 ⇒ 1st row old serial number 2 (actually 2nd row serial number 2) → serial number 1
If i look at the change date, it will be 3 → 2 2 → 1, so if you can specify the update order,
The order is specified by the change date, and the second column of customer TBL is "1001" "1" "1001003"
I think that I can not.
In this case, the change date cannot be tracked under the condition of the latest item, so
All records must be updated in sequence.

Customer TBL

ID Serial number Card number Gender name
0001 1 0001001 M Taro
0001 3 0001001 M Taro
0005 4 0001010 F Hanako
UPDATE customer SET
    customer.ID = changes.ID
    , customer.serial number = changes.serial number
    , customer.card number = changes.card number
FROM customer TBL AS customer
INNER JOIN change information TBL AS changes ON
    customer.card number = changes.old card number AND
    customer.serial number = changes.old serial number;
  • Answer # 1

    In the join condition orWHERE clause of the change information TBL, if you add a condition that specifies the data with the "oldest serial number and the oldest card number with the same modification date" data, the old serial number becomes 3 old card numbers The data with 00010001 can also be updated with the data of change date 2018/08/20.

    INNER JOIN change information TBL AS changes ON
        customer.card number = changes.old card number AND
        customer.serial number = changes.old serial number
        changes.change date = (subquery to get the oldest serial number, the largest change date in the same data with the old card number);


    I think there are many ways to write subqueries, so please think for yourself.


    ↓↓↓ Addition ↓↓↓
    It seems that it can be solved by using WITH RECURSIVE as sazi's answer.

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

    OKYAKUSAMA and HENNKOU in the WITH clause only represent the customer TBL and change information TBL inline, so please skip them. (Please understand that it was cumbersome to create a table in our environment.)
    The TAISYO part uses the WITH RECURSIVE syntax.
    First, in the non-recursive expression part, the same data is extracted from the customer TBL key and the change information TBL old key. Here, the customer information key is held as the original key (MOTOID, MOTORENNBANN, MOTOCARDNO). Furthermore, DEPTH representing depth is defined as 1.
    In the recursive expression part, the key of the change information TBL obtained as a result of the non-recursive expression part is extracted from the same data as the old key of the change information TBL, and this is repeated. DEPTH is added each time it is repeated.
    The result is as follows:

    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


    From this result in the SELECT clause after WITH, you can obtain the update target and its update value by obtainingdata with the same key and original key as the customer TBL and the maximum depth.

    I think there are other ways to do this, but this was the limit in my unattended SQL. . .
    I think that there are many parts that are difficult to understand, and this is not the only way to do it, so find a method that suits you and solve the problem.

  • Answer # 2

    Isn't it necessary to process the change information TBL sequentially instead of updating it all at once?

    Please check the SQL procedure language and cursor in the V server programming manual.

  • Answer # 3

    If the change information table is the change of the key to be changed, and if the consistency is guaranteed (no duplicate keys) when looking at the beginning and end of all changes,WITH RECURSIVE, you can consolidate from start to end in one line, so I think you should update based on that.

  • Answer # 4

    Basically, "I will update the data with the extra information omitted".
    In other words, the task of "Find rows with the last modified date for each old serial number" is performed first.
    There are quite a few ways to do it.

    ・ Use "window function"
    ・ Use "join lateral"
    stackoverflow

    Other unverified

    Search for data with the same old serial number that has no change date after your own change date

    select *
    from Change information TBL as A
    where not exists (
      select 0
      from Change information TBL as B
      where B. Old serial number = A. Old serial number
        and B. Date of change>A. Date of change
    )

    After that, search for data with the same modification date as the maximum modification date between the same old serial numbers.

    You can update based on these. If duplicate data disappears, this is the case.