Help with complex DELETE requested 1

[VickyC]

greetings to all

I hope I'm not wearing out my welcome in this forum, but I am struggling with an SQL problem.

From a table like this...[tt]

ID C1 C2 C3 C4 C5
1 25 26 11 56 50
2 28 44 49 49 11
3 49 44 13 44 44
4 11 49 49 44 28
5 28 13 28 56 11
etc [/tt]

... I want to delete ROWS that are REPEATED in REVERSE. In the table shown, the 4th record must be deleted because its values (11,49,49,44,28) are in REVERSE order to those of the 2nd record (28,44,49,49,11). The output in this simple case would show rows 1, 2, 3 and 5.

I don't doubt that I could do this using VBA, but it would be slow. My actual tables have 10 data columns and a huge number of records.
Is there an SQL approach that might help here? I'm getting better at SQL, but this is still beyond me.

thanks in advance for any thoughts
Vicky C.

 

[VickyC]

I'd like to add something to my original post. EACH row in the table has exactly one reverse copy that has to be deleted. One table, for example has 907,200 rows, but 453,600 of them need to be deleted. This is true for each table I have to work with.

The rows and their reverse images are interspersed somewhat randomly, so this isn't just a matter of, say, discarding the bottom half of the table.

Thanks
Vicky C.

 

[ByteMyzer]

You might try something like the following (substitute highlighted text with actual table/field names):

DELETE T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE EXISTS (
    SELECT T2.[b][i]ID[/i][/b]
    FROM [b][i]MyTable[/i][/b] T2
    WHERE T2.[b][i]ID[/i][/b] < T1.[b][i]ID[/i][/b]
    AND T2.[b][i]C10[/i][/b] = T1.[b][i]C1[/i][/b]
    AND T2.[b][i]C9[/i][/b] = T1.[b][i]C2[/i][/b]
    AND T2.[b][i]C8[/i][/b] = T1.[b][i]C3[/i][/b]
    AND T2.[b][i]C7[/i][/b] = T1.[b][i]C4[/i][/b]
    AND T2.[b][i]C6[/i][/b] = T1.[b][i]C5[/i][/b]
    AND T2.[b][i]C5[/i][/b] = T1.[b][i]C6[/i][/b]
    AND T2.[b][i]C4[/i][/b] = T1.[b][i]C7[/i][/b]
    AND T2.[b][i]C3[/i][/b] = T1.[b][i]C8[/i][/b]
    AND T2.[b][i]C2[/i][/b] = T1.[b][i]C9[/i][/b]
    AND T2.[b][i]C1[/i][/b] = T1.[b][i]C10[/i][/b]
);

 

[JonFer]

A variation of ByteMyzer's solution - if your ID field is a primary key to the table then Access should allow you to do this:

DELETE T1.*
FROM MyTable T1 Inner Join MyTable T2
WHERE T2.ID < T1.ID
AND T2.C10 = T1.C1
AND T2.C9 = T1.C2
AND T2.C8 = T1.C3
AND T2.C7 = T1.C4
AND T2.C6 = T1.C5
AND T2.C5 = T1.C6
AND T2.C4 = T1.C7
AND T2.C3 = T1.C8
AND T2.C2 = T1.C9
AND T2.C1 = T1.C10

 

[ByteMyzer]

Hello, JonFer,

You have an incomplete INNER JOIN clause; your query will not work.

 

[JonFer]

Corrected syntax:

DELETE T1.*
FROM MyTable T1 Inner Join MyTable T2
On T2.C10 = T1.C1
AND T2.C9 = T1.C2
AND T2.C8 = T1.C3
AND T2.C7 = T1.C4
AND T2.C6 = T1.C5
AND T2.C5 = T1.C6
AND T2.C4 = T1.C7
AND T2.C3 = T1.C8
AND T2.C2 = T1.C9
AND T2.C1 = T1.C10
Where T2.ID < T1.ID

 

[ByteMyzer]

Hello, JonFer,

That will not work, either. You will get a "Could not delete from specified tables" error.

If you stay with the original query syntax I provided, you will find it meets the requirement.

 

[JonFer]

You're right - I should have tested it since Delete rules in Access are so arbitrary.

This works in SQL Server but not Access. Access only allowed it when you inner join (ID=ID) on the primary/unique keys (which wouldn't help here) and even then it did not allow a self-join. Hopefully later versions of Access will be more flexible with Deletes.