SQL to delete subset entries-CodePudding

Imagine a table which has one entry with "I pledge allegiance to the Flag of the United States.", and another entry with "I pledge allegiance"

Looking for the SQL to delete from the same table the shorter entry which is a subset of the longer entry.

I have this, but it is not working:

DELETE FROM mytable T1
WHERE EXISTS (SELECT id FROM mytable T2 WHERE T2.Column1 like '%T1.Column1%')

Any help would be appreciated.

CodePudding user response：

You can use SELF JOIN for this case in next way:

DELETE T1.*
FROM mytable T1
JOIN mytable T2 ON T2.col1 like CONCAT('%', T1.col1, '%')
WHERE T1.id <> T2.id;

You can test this approach here: https://sqlize.online/sql/mysql80/f88299d89532e9c6b3a251b071a8a541/

CodePudding user response：

Something like this?

DELETE mytable
WHERE id in (
    SELECT t1.id
    FROM mytable t1
    JOIN mytable t2
        ON t1.column1 like '%' || t2.column1 || '%'
        AND LENGTH(t1.column1) < LENGTH(t2.column1)
)