Hi all,
I have a table which didn't have any primary key until today.
As a result I have multiple rows with the same column values.
CREATE TABLE table_a (ISSUE_DATE, ID, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY, VAL )
AS
SELECT 91122, 573998, 'CH', 'IT', 'AC', 'AP', 250881866, 42 FROM DUAL UNION ALL
SELECT 91122, 573993, 'GR', 'IT', 'AC', 'AP', 250881861, 32 FROM DUAL UNION ALL
SELECT 91122, 573993, 'GR', 'IT', 'AC', 'AP', 250881861, 32 FROM DUAL UNION ALL
SELECT 91122, 573993, 'GR', 'IT', 'AC', 'AP', 250881861, 32 FROM DUAL UNION ALL
SELECT 100118, 1989450, 'GR', 'PL', 'AC', 'AP', 1563102066, 1 FROM DUAL UNION ALL
SELECT 100118, 1989450, 'GR', 'PL', 'AC', 'AP', 1563102066, 1 FROM DUAL UNION ALL
SELECT 231130, 575960, 'MK', 'GR', 'AD', 'AP', -1117000000, 1 FROM DUAL UNION ALL
SELECT 231130, 575960, 'MK', 'GR', 'AD', 'AP', -1117000000, 1 FROM DUAL UNION ALL
SELECT 231130, 575960, 'MK', 'GR', 'AD', 'AP', -1117000000, 17 FROM DUAL UNION ALL
SELECT 231130, 575960, 'MK', 'GR', 'AD', 'AP', -1117000000, 17 FROM DUAL UNION ALL
SELECT 231129, 575960, 'MK', 'GR', 'AD', 'AP', -1145000000, 12 FROM DUAL UNION ALL
SELECT 231129, 575960, 'MK', 'GR', 'AD', 'AP', -1145000000, 3 FROM DUAL;
I want to create a primary key on columns ISSUE_DATE, ID, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY.
So, the problem is the value of column val.
If the value of val is the same for every ISSUE_DATE, ID, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY , I want just to keep one row and delete the others.
Otherwise, if I have same ISSUE_DATE, ID, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY with different values on column val, first I check on table_b and get count(*) from a table_B joining the columns MIN_DATE, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY, else I keep the max value.
The structure and some sample data of table_b is
CREATE TABLE table_B (MIN_DATE, ID, SENDER, RECIPIENT, TYPE_A, TYPE_B, KEY)
AS
SELECT TO_TIMESTAMP('25/09/2023 00:13;15', 'DD/MM/YYYY HH24:MI:SS'), 573998, 'CH', 'IT', 'AC', 'AP', 4534 FROM DUAL UNION ALL
SELECT TO_TIMESTAMP('22/11/2022 02:19;16', 'DD/MM/YYYY HH24:MI:SS'), 573998, 'EL', 'IT', 'AC', 'AP', 34536 FROM DUAL UNION ALL
SELECT TO_TIMESTAMP('30/11/2023 02:20;16', 'DD/MM/YYYY HH24:MI:SS'), 575960, 'MK', 'GR', 'AD', 'AP', -1145000000 FROM DUAL UNION ALL
SELECT TO_TIMESTAMP('30/11/2023 02:25;16', 'DD/MM/YYYY HH24:MI:SS'), 575960, 'MK', 'GR', 'AD', 'AP', -1145000000 FROM DUAL;
As output I would like
SELECT 91122, 573998, 'CH', 'IT', 'AC', 'AP', 250881866, 42 -- IS NOT DUPLICATE
SELECT 91122, 573993, 'GR', 'IT', 'AC', 'AP', 250881861, 32 -- ALL THE VALUES WERE 32, we kept one row
SELECT 100118, 1989450, 'GR', 'PL', 'AC', 'AP', 1563102066, 1 -- ALL THE VALUES WERE 1, we kept one row
SELECT 231129, 575960, 'MK', 'GR', 'AD', 'AP', -1145000000, 12 -- THE VALUES WERE 3,12 , on table_B we did not find any match, we kept the max
SELECT 231130, 575960, 'MK', 'GR', 'AD', 'AP', -1117000000, 2 -- We found 2 rows on table_B
Could you help me please ?
Thanks in advance