Hi, Thank you very much.
Can task 2 and 3 be performed in one query to improve the performance? Thanks > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter > Sent: Friday, February 19, 2016 3:23 PM > To: 'SQLite mailing list' > Subject: Re: [sqlite] Process duplicate field values > Sensitivity: Confidential > > Basic idea, no testing > > The core query is > > Select F1 as key,count() as count from MyTable group by F1 having count>1; > > The number of distinct F1 values is > > select count() from (<subquery>) > > The number of conflict records is > > Select sum(count) from (<subquery>) > > And the update would be > > Update MyTable set F2=9 where F1 in select key from (<subquery>) > > The core query could also be declared as a view or as a CTE > > -----Urspr?ngliche Nachricht----- > Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] Im Auftrag von admin at shuling.net > Gesendet: Freitag, 19. Februar 2016 07:01 > An: sqlite-users at mailinglists.sqlite.org > Betreff: [sqlite] Process duplicate field values > Wichtigkeit: Hoch > Vertraulichkeit: Vertraulich > > Hi, > > > > I create a table as follows: > > > > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER); > > > > Then add the following records: > > > > INSERT INTO MyTable (F1, F2) Values (1, 2); > > INSERT INTO MyTable (F1, F2) Values (1, 3); > > INSERT INTO MyTable (F1, F2) Values (2, 4); > > INSERT INTO MyTable (F1, F2) Values (2, 5); > > INSERT INTO MyTable (F1, F2) Values (3, 6); > > INSERT INTO MyTable (F1, F2) Values (3, 7); > > INSERT INTO MyTable (F1, F2) Values (4, 2); > > > > Now if two records have the same value of F1, then I will define them as > conflict records. > > > > Now I need to perform the following tasks: > > > > 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. > 3. Set the F2 value of all the conflict records to 9. Keep all other > records > intact. > > > > How to do that? Can task 2 and 3 be implemented in one SQL query to > improve the performance? > > > > Thanks > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original communication. > Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
