Reputation:
I have this table in a SQLite database:
-----------------------------
id | string |...other columns
-----------------------------
01 | AA |...
02 | AAB |...
03 | AB |...
04 | BAB |...
What I need to do is to remove all the rows where the cell value of the column "string" is contained in any other row.
In the example above, row id-1 string is contained in row id-2 string and row id-3 string is contained in both row id-2 and id-4 strings.
So the final result should be:
-----------------------------
id | string |...other columns
-----------------------------
02 | AAB |...
04 | BAB |...
Is there an easy query to perform this operation?
Upvotes: 1
Views: 647
Reputation: 44611
You can use exists
(case–insensitive):
delete from table
where exists (select * from table t where t.string like '%' || table.string || '%')
or instr
(case–sensitive):
delete from table
where exists (select * from table t where instr(t.string, table.string) > 0)
Upvotes: 0