> > 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.
But this is extremely inefficient as well. Since each record is, in fact, an update, you're actually performing a delete/insert for each record, activating any recursive triggers you have as well. On top of that, any UPDATE trigger would not fire. Now, if it were an 'insert or update' it might better. If the syntax cannot be modified to add either UPDATE FROM or the Oracle syntax (which I like very much), then I wonder if it would be possible for the parser to detect when expressions will return the same rows. I know it couldn't work all the time, but could it handle the simple case where each expression is SELECT column FROM/JOIN/WHERE and the FROM/JOIN/WHERE is the same for each? Marc _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
