Delete rows where string value appears inside another row value that is larger

I am trying to make a table smaller by removing values that appear inside other larger values. e.g.

     Column
Row1 potato234
Row2 to2
Row3 ot
Row4 potatos

The outcome I want is for row 2 and 3 to be deleted from the table as they both appear inside of another row that is larger than them.

I have tried to write code for it but without being able to use Inner Joins in the DELETE statement I don’t know how to do it without having a subquery return multiple elements.

DELETE FROM dbo.ENTRY_VALUES
WHERE LENGTH(ENTRIES) < (SELECT LENGTH(ENTRIES) FROM dbo.ENTRY_VALUES)
AND ENTRIES LIKE ('%' || (SELECT ENTRIES FROM dbo.ENTRY_VALUES) || '%')

Answer

You should be able to use:

delete from entry_values
   where exists (select 1
                 from entry_values ev2
                 where ev2.entry like '%' || entry_values.entry || '%' and
                       ev2.entry <> entry_values.entry
                );

Advertisement

Answer