SqLite Delete command with subquery

Question

I have following query:

SELECT * FROM (
    SELECT * FROM pojmovi WHERE pojam LIKE '%og'
) WHERE pojam NOT LIKE '%olog';

This works as expected, selecting all from column pojmovi that ends in 'og' but not in 'olog', now, I want to delete those results, so I'm trying something like:

DELETE FROM (
    SELECT * FROM pojmovi WHERE pojam LIKE '%og'
) WHERE pojam NOT LIKE '%olog';

And this query causes near "(": syntax error Is this somehow possible in sqlite?

score 2 · Answer 1 · answered Aug 24 '11 at 11:19

2

This can't possibly work as you want - you attempt to delete from an intermediate query result and that makes no sense and is not allowed. You need to specify an actual table after DELETE FROM. Something like this:

 DELETE FROM pojmovi WHERE ( pojam LIKE '%og' ) AND (pojam NOT LIKE '%olog' );

answered Aug 24 '11 at 11:19

sharptooth

167,383
100
513
979

Thanks for your help and time. After little experimenting, I came up with this query that works as expected, as far as I can tell: `DELETE FROM pojmovi WHERE id IN ( SELECT id FROM ( SELECT * FROM pojmovi WHERE pojam LIKE '%og' ) WHERE pojam NOT LIKE '%olog' )` – realitylord Aug 24 '11 at 11:45

score 0 · Answer 2 · answered Aug 24 '11 at 11:22

How about

SELECT * FROM pojmovi WHERE pojam LIKE '%og' and pojam NOT LIKE '%olog';

and

DELETE FROM pojmovi WHERE pojam LIKE '%og' and pojam NOT LIKE '%olog';

Your queries will be slow because they cannot use an index (because you are doing LIKE "%...")

SqLite Delete command with subquery

2 Answers2