No. Originally I think since task 2 and 3 are operations performed on the same set of records, maybe they can be merged to improved the performance though one is get and another is set.
Thank you very much > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik > Sent: Friday, February 19, 2016 11:04 PM > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Process duplicate field values > > On 2/19/2016 1:00 AM, admin at shuling.net wrote: > > 1. For all conflict records, get the total count of distinct F1 values. > > In the above sample, record 1, 2, 3, 4, 5, 6 are conflict records, but > > the distinct values are only 1, 2, 3 so the total count should be 3. > > 2. Get the total count of all the conflict records. In the above > > sample, it should be 6. > > select count(*) CountOfConflictGroups, sum(c) CountOfConflictRecords from > ( > select count(*) c from MyTable group by F1 having count(*) > 1 ); > > > 3. Set the F2 value of all the conflict records to 9. Keep all > > other records intact. > > update MyTable set F2=9 where F1 in > (select t.F1 from MyTable t group by t.F1 having count(*) > 1); > > > Can task 2 and 3 be implemented in one SQL query > > No. One is a "get", the other is a "set". A single SQL query can't do both. Did > you mean tasks 1 and 2, perhaps? > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
