CODEWITHSUNDEEP
sqlsqlite
user1344280
user1344280

Reputation:

SQLite - How to remove rows that have a string cell value contained in other rows?

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

Answers (1)

potashin
potashin

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

Related Questions