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
