Hi,
Is it possible to merge two rows with SQLite? Say I have the following table":
CREATE TABLE foo(a integer, b integer, c integer, d integer);
CREATE INDEX idx ON foo(a,b);
With the following data:
INSERT INTO foo VALUES (1,1,1,2);
INSERT INTO foo VALUES (1,2,3,4);
INSERT INTO foo VALUES (1,2,5,6);
INSERT INTO foo VALUES (2,1,7,8);
INSERT INTO foo VALUES (3,1,9,10);
INSERT INTO foo VALUES (3,2,11,12);
INSERT INTO foo VALUES (3,2,13,14);
INSERT INTO foo VALUES (3,3,15,16);
What I would like is that when the combenation of a and b are not unique to
merge the two rows to one and sum the values b and c. So the resulting table
should become:
a | b | c | d
---+---+---+---+
1 | 1 | 1 | 2
1 | 2 | 8 | 10
2 | 1 | 7 | 8
3 | 1 | 9 | 10
3 | 2 | 24| 26
3 | 3 | 15| 16
I know how to select the two duplicate rows (ensum them, but. I use the query:
SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them.
But how do I merge them? Do I need a self written program to itterate throuw the result of the previous query to merge them or can this be done
on the sqlite3 program with a few "simple" querys?
Thanks in advance!
Kind Regards
Daan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------