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|
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.
|ID||Serial number||Card number||Gender||name|
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"
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.
- how to specify the sort order when using sorted in python
- add update process in c # save button
- Example of three ways to specify the execution order of threads in Java
- Detailed process of python connection to PostgreSQL database
- cloud firestore - update process does not run for the entire collection in firestore
- update process for mysql slave
- c # - update characters in postgresql
- i don't know the order of programming process in object-oriented php
- ruby - update process when duplicated at create
- i want to specify the load order of resoucesloadall of unity
- mysql - update process of db in springboot and thymleaf becomes insert instead of update
- postgresql - not in kana order in production environment
- postgresql - i want to join another table with postgres and update it
- i want to update the db value in postgresql
- python - django i want to process new creation and update on the same page
- perl - when using update with postgresql, setting integer column ='' results in an error
- process where postgresql procedure does not work
- Workaround for timestamp does not update automatically when PostgreSQL updates table
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved