RE: [sqlite] Re: Merge two rows/records
Hi Daan, You can make the columns (a, b) unique across (a, b), but not separately unique; by that whenever you are trying to insert a row with same (a, b) combination it will give an error and at that time you can update the column values c and d. I hope this will solve your problem. Regards, Phani -Original Message- From: Daan van der Sanden [mailto:[EMAIL PROTECTED] Sent: Monday, October 08, 2007 3:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] Re: Merge two rows/records Thanks for the quick reply! Simon Davies writes: > > sqlite> select a,b,sum(c),sum(d) from foo group by a,b; > > > > gives you the data you are after. This could be used to populate > > another table via > > > > sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by > > a,b; > > > > Of course, if you can get the data you want from your existing table > > using a simple query, you may not actually need a new table. At the moment I've got a database with values gathered from multiple inputs that generated "duplicate entries" for the "what should be unique" a,b combination. So I was wondering if they could be "easily" merged without creating a new table. Now I'm going to first copy all unique samples to a new database and then insert the summed values using the given query. But this solution seems a bit awkward, since I'm copying 6 million unique records to a new database and adding a small 22.000 records that are summed. So that's why I was wondering if it could be done in the same table. I hope my problem is a bit clearer now. Kind regards Daan - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Merge two rows/records
Hi Daan, You could try this: sqlite> CREATE TABLE foo(a integer, b integer, c integer, d integer, ...> unique (a, b) on conflict ignore); sqlite> CREATE TRIGGER fooUnique before insert on foo ...> when exists (select a from foo where a=new.a and b=new.b) ...> begin update foo set c=c+new.c, d=d+new.d where a=new.a and b=new.b; ...> end; sqlite> sqlite> sqlite> INSERT INTO foo VALUES (1,1,1,2); sqlite> INSERT INTO foo VALUES (1,2,3,4); sqlite> INSERT INTO foo VALUES (1,2,5,6); sqlite> INSERT INTO foo VALUES (2,1,7,8); sqlite> INSERT INTO foo VALUES (3,1,9,10); sqlite> INSERT INTO foo VALUES (3,2,11,12); sqlite> INSERT INTO foo VALUES (3,2,13,14); sqlite> INSERT INTO foo VALUES (3,3,15,16); sqlite> sqlite> select * from foo; 1|1|1|2 1|2|8|10 2|1|7|8 3|1|9|10 3|2|24|26 3|3|15|16 sqlite> Rgds, Simon On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote: > Thanks for the quick reply! > . . . > At the moment I've got a database with values gathered from multiple > inputs that generated "duplicate entries" for the "what should be unique" > a,b combination. So I was wondering if they could be "easily" merged > without creating a new table. > > Now I'm going to first copy all unique samples to a new database and then > insert the summed values using the given query. But this solution seems a > bit awkward, since I'm copying 6 million unique records to a new database > and adding a small 22.000 records that are summed. So that's why I was > wondering if it could be done in the same table. > > I hope my problem is a bit clearer now. > > Kind regards > Daan > - To unsubscribe, send email to [EMAIL PROTECTED] -
