On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Unfortunately, there are no good alternatives for this statement. I wish > SQLite supported UPDATE ... FROM syntax (non-standard, used by some other > DBMS). Here's one not-so-good alternative: > > insert or replace into t1(id, a, b, otherColumns) > select t1.id, t1.a + t2.a, t1.b + t2.b, t1.otherColumns > from t1 left join t2 on (t1.id = t2.id); > > (this assumes id column is declared unique, or primary key; replace with > ROWID otherwise). I suspect this cure might be worse than the disease.
I use this all the time as it's the only way in SQLite3 to do UPDATEs with JOINs. I use it with an INTEGER PRIMARY KEY column. SQLite3 tables always have a rowid somehow that's suitable for this, but it's not a good assumption to bake into an application -- the application should make sure that there's an INTEGER PRIMARY KEY or other suitable PRIMARY KEY or UNIQUE columns. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
