Hello. It is necessary to remove duplicates in sqlite. I wrote such a query, but I write an error like no such column tmp.id. The tmp table is a complete duplicate of the variant. Huge please help.

delete from variant WHERE (variant.id < tmp.id ) AND (variant.user_id=tmp.user_id) AND (variant.test_id=tmp.test_id)

    3 answers 3

    There is no such table in your request.

    I do not know sqlite syntax in sqlite, in MS SQL I would rewrite the query like this:

     delete from v from variant v inner join tmp t on v.user_id=t.user_id AND v.test_id=t.test_id WHERE v.id < t.id

      tmp.id

      Where does the tmp table come from in the query?

      • it is in the database. in mysql, this would definitely work. And what's wrong with sqllite? - Kostya Samsin

      I did not understand what kind of duplicates we are talking about, and I do not know the sqlite dialect, but try this option:

       delete from variant WHERE exists (select 1 from tmp where variant.id < tmp.id AND variant.user_id=tmp.user_id AND variant.test_id=tmp.test_id )