jeudi 18 février 2016

Process duplicate field values in SQLite

I am using SQLite 3.11. 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 conflict records. In the above sample, it should be 6.
  3. Set the F2 value of 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

Aucun commentaire:

Enregistrer un commentaire