How to delete duplicates if all the their linked values in another table (foreign key linked rows) are also the same

Question

I have three tables:

x (column1,..., x_id)

y (column1, column2, colum3, ..., x_id, y_id)

z (column1, column2, colum3,..., x_id, y_id, z_id)

My final goal: delete duplicates in y table if all of their linked values (based on foreign keys) in z table are also exactly the same. For example, This is just an example.

For more clarification consider this example: y table: my table structure is very similar to this and this table is close to the reality of what I have.

column 1	column 2	x_id	y_id
foo	bar	1	1
foo	bar	1	2
xx	yy	2	3
zz	kk	2	4
tt	mm	2	5
baz	qux	3	6
baz	qux	3	7

has two groups with the same column 1 and column 2 and x_id values (the y_ids = (1 and 2) and y_id == (6,7).

Then for these rows, I want to check a second table to see if all the items are the same. meaning they have the same items in the z table. The items of the y_id = 1 and y_id = 2 are the same, so one of them in the y table should be deleted.

{color:red;} column 1	column 2	column	x_id	y_id	z_id
foo	bar	qux	1	1	1
foo2	bar2	qux2	1	1	2
foo	bar	qux	1	2	3
foo2	bar2	qux2	1	2	4
baz	foo	qux	3	6	5
baz2	foo2	qux1	3	6	6
baz3	foo3	qux2	3	6	7
baz	foo	qux	3	7	8
baz2	foo2	qux1	3	7	9

I want to delete the row with y_id = 1 from the y table ( or y_id = 2, which duplicate row doesn't make a difference). The tables are very big so a fast solution would be great. I know I can join the tables, but I was hoping I could find another solution. A solution that can use the group by IDs instead and just look for the found items there inside the z table (third table)

I want to delete one instance of duplicates, but not only based on the first table columns, after duplicates are found there, check for their items in table z and then see if the query result based on the ids are actually the same (the second comparison also is done based on certain columns). I don't know how to put those ids I have in the first group in the query that checks for the duplicates in the second table. — DRA
– DRA, Commented Aug 21, 2023 at 21:22
@Alex I really don't think I am asking for something that hard. If we have three tables x y z, group the y records based on the foreign key of x inside y table, and in each group check if they have duplicates, if they do, then we want to check z table, in which we have the y foreign keys, so based on y keys of those duplicates in z, check if they have exactly the same items in z. like if we have a list for each, regardless of order, the items are the same. that means they are complete duplicates. but if they have different items in z they just have similar names in y table. — DRA
– DRA, Commented Aug 23, 2023 at 12:00
@Alex There are usually paired duplicates in each group in y table if there are any, so we can easily compare them. Anyway, I found a solution myself. — DRA
– DRA, Commented Aug 23, 2023 at 12:00

DRA · Accepted Answer · 2023-08-22 20:41:44Z

I believe the below example will help me to delete the rows I want:

WITH cte AS (
    SELECT
        a.column1 AS a_column1,
        a.column2 AS a_column2,
        a.y_id AS a_y_id,
        a.x_id AS a_x_id,
        b.column1 AS a_column1,
        b.colum2 AS a_column2,
        b.y_id AS b_y_id ,
        b.x_id AS b_x_id 
    FROM YTable a
    JOIN YTable b
    ON a.column1 = b.column1 
        AND a.column2 = b.column2 
        AND a.x_id = b.x_id 
        AND a.y_id != b.y_id
    WHERE a.y_id < b.y_id
)
SELECT
    cte.*,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM (
                SELECT * FROM ZTable
                WHERE y_id = cte.y_id
            ) table1
            FULL JOIN (
                SELECT * FROM ZTable
                WHERE y_id = cte.y_id
            ) table2
            ON table1.colum1 = table2.column1
                AND table1.colum2 = table2.colum2
                AND table1.colum3 = table2.colum3
            WHERE table1.column1 IS NULL OR table2.column1 IS NULL
        )
        THEN 'Not Identical'
        ELSE 'Identical'
    END AS duplicated
FROM cte;

Collectives™ on Stack Overflow

How to delete duplicates if all the their linked values in another table (foreign key linked rows) are also the same

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related