How to solve SQLite error `near "bogus_table": syntax error: `?

Question

I'm doing project where I have a table which has hash and timestamp columns. And the task is to delete all the rows which are hash duplicates, leaving the ones with oldest timestamp. I found a similar question on stack and I tried to adopt the given snippet to my needs, but I got an error in sqlite near "shop_bogus": syntax error:

My adopted snippet:

DELETE  shop_bogus
FROM    shop_bogus b
JOIN    (
    SELECT  MIN(a.timestamp) timestamp,
            hash
    FROM    shop_bogus a
    GROUP   BY a.hash
) c ON
    c.hash = b.hash
AND c.timestamp <> b.timestamp

What am I doing wrong?

Possible duplicate of [Deleting duplicate rows from sqlite database](http://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database) — Murphy, Jun 07 '16 at 11:51

score 0 · Answer 1 · answered Jun 07 '16 at 11:31

0

The answer you ripped the statement from is for MS SQL Server; SQLite doesn't support this syntax for DELETE.

answered Jun 07 '16 at 11:31

Murphy

3,827
4
21
35

Oh, is it even possible to make such query within sqlite or mysql db? – Михаил Павлов Jun 07 '16 at 11:46
@МихаилПавлов Have you tried searching for "sqlite delete duplicates"? – Murphy Jun 07 '16 at 11:51
Yes; didn't work for questioners who refuse to read, understand, or doing their part. – Murphy Jun 08 '16 at 07:43
look what an awesome answer I got on reddit https://www.reddit.com/r/learnSQL/comments/4mx5nu/need_help_with_building_query/ – Михаил Павлов Jun 09 '16 at 03:40

How to solve SQLite error `near "bogus_table": syntax error: `?

1 Answers1