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:
- 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.
- Get the total count of conflict records. In the above sample, it should be 6.
- 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