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.
