Combing DELETE and LIKE in sqlite statement

Question

I am trying to combine the two in one status, I would even agree to two separate statements ..... I know that this should be possible, but how?

here is what i tried:

DELETE FROM myTable WHERE myValue LIKE 'findme%'; 

and

 DELETE FROM myTable WHERE EXISTS (SELECT * FROM myTable WHERE myValue LIKE 'findme%'); 

I get an error for the second statement that says something like: you can only have one result for the LIKE statement with a different expression ...

Answer 1

Your first expression should work. Try without a final semicolon, maybe?

The second option is illogical. Your subquery does not correlate with the first, and you cannot scan a table that changes. In fact, what I would expect from this query, if it were ever executed, is that all rows are deleted if there is one row that your column is in ...

If you're wondering why the solutions with the IN clause work, rather than EXISTS , this is because the EXISTS clause is evaluated for each row, while the IN set is evaluated once.

Answer 2

If this statement returns any rows

 select * from mytable where myvalue like 'findme%'; 

then replace "select *" with "delete" to delete them.

 delete from mytable where myvalue like 'findme%'; 

This should work with any SQL database if you have sufficient permissions.

Answer 3

Have you tried this?

 DELETE FROM myTable WHERE myValue IN (SELECT myValue FROM myTable WHERE myValue LIKE 'findme%'); 

Answer 4

This works for me:

 DELETE FROM myTable WHERE id IN (SELECT id FROM myTable WHERE myValue LIKE 'findme%'); 

Answer 5

  DELETE FROM `table_name` WHERE` column_name` LIKE 'value%'