combing DELETE and LIKE in a sqlite statement

Question

I am trying to combine the two into a single statment, I would even settle for two seperate statements..... I know it must be possible, but how?

this is what I have 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 saying something like, you can only have a single result for a LIKE statement with another statement...

Answer 1

If this statement returns any rows

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

then replacing "select *" with "delete" should delete them.

delete from mytable where myvalue like 'findme%';

That should work with any SQL database, as long as you have sufficient permissions.

Answer 2

Your first statement should work. Try without the final semicolon maybe?

The second one is not logical. Your subquery is not correlated to the first one and you cannot scan a table which is being modified. Actually what I would expect from this query, if it would ever run, is that all rows are deleted if there is a single row where your column matches…

If you wonder why the solutions with the IN clause work and not the EXISTS, it is because the EXISTS condition is evaluated for each row, whereas the IN set is evaluated once.