I am trying to figure out an sql query or queries that will do the following:
CREATE TABLE (Key_Fields, Start, End, Value_Fields,
Primary Key(Key_Fields, Start, End));
For any pair of records where the Key_fields are the same, and the Start of the
one is the same as the end of the other, then if the value_fields are the same,
then remove the two and replace with one entry with the non-common start and
end values.
So:
A | 01 | 02 | 100
A | 02 | 00 | 100
B | 01 | 02 | 100
B | 02 | 00 | 200
C | 02 | 00 | 100
would produce in the end
A | 01 | 00 | 100 < combined record
B | 01 | 02 | 100
B | 02 | 00 | 200
C | 02 | 00 | 100
To make things more complicated, the key_fields are actually a combination of 8
fields, and there are 5 value_fields.
To make things easier, perhaps, there is only one value in the start or end
that can be combined, in this example, that would be '02'. The table is likely
to have around 30,000 records.
The best solution I have come up with is to combine, or not, the records in a
temporary table, delete from the first table, then return the records from the
temp table, but that seems wasteful.
Am I missing an obvious way to do what I want?
Thanks,
David
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
