On 23 November 2011 11:23, nadavius <nadav...@yahoo.com> wrote: > > Hi guys, > > I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), > Count(INT), Value(INT)] > I would like to merge the content of T1 into T2 using the following ruels: > 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and > SK) do not exist in T2 > 2. In case that there are common rows between T1 and T2, where common means > T1[Index1, Index2] and T2[Index1, Index2] are the same - update T2.Count and > T2.Value ONLY IF T1.Count has a greater value > > I am looking for an efficient SQLite statement to make it work.
Can't speak for its efficiency, but this appears to do what you want: SQLite version 3.4.2 Enter ".help" for instructions sqlite> create table t1( i1 integer, i2 integer, cnt integer, val integer, primary key( i1, i2 ) ); sqlite> create table t2( i1 integer, i2 integer, cnt integer, val integer, primary key( i1, i2 ) ); sqlite> insert into t1 values( 1,1,1,1 ); sqlite> insert into t1 values( 2,1,2,2 ); sqlite> insert into t1 values( 3,1,1,3 ); sqlite> insert into t1 values( 4,1,5,4 ); sqlite> sqlite> insert into t2 values( 1,1,0,111 ); sqlite> insert into t2 values( 4,1,6,444 ); sqlite> sqlite> create trigger t2_from_t1 before insert on t2 begin update t2 set cnt=new.cnt, val=new.val where t2.i1=new.i1 and t2.i2=new.i2 and new.cnt > t2.cnt; end; sqlite> sqlite> select * from t1; 1|1|1|1 2|1|2|2 3|1|1|3 4|1|5|4 sqlite> select * from t2; 1|1|0|111 4|1|6|444 sqlite> insert or ignore into t2 select i1, i2, cnt, val from t1; sqlite> sqlite> select * from t1; 1|1|1|1 2|1|2|2 3|1|1|3 4|1|5|4 sqlite> select * from t2; 1|1|1|1 <== updated 4|1|6|444 <== unchanged 2|1|2|2 <== inserted 3|1|1|3 <== inserted sqlite> > > Any ideas? > > Nadav. Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
