Hello, I came across this juicy nugget when trying to optimize 'update' and 'delete' statements that involve multiple tables. Normally I would use 'join' but H2 doesn't support join for update and delete (at least not directly).
I was lucky to discover this technique. Searching around the Internet wasn't much help so I thought I'd post here. My delete statement removed 31k out of 45k records. This change reduced the 14 sec operation down to about 3 sec. <pre> -- SETUP drop table if exists t1, t2; create table t1 (id int PRIMARY KEY, flag boolean); insert into t1(id) values(1); insert into t1(id) values(2); create table t2 (id int PRIMARY KEY); insert into t2 values(1); -- UPDATE -- slow update t1 set flag = true where id in (select id from t2); -- fast but has side effect of setting non-joining records to 'null' <<<<<<<<<<<<<<<<<<<<<<< update t1 set flag = (select true from t2 where t2.id = t1.id); -- where <some clause to reduce side effect> -- DELETE -- slow delete from t1 where id in (select id from t2); -- fast <<<<<<<<<<<<<<<<<<<<<<<<<<<<< delete from t1 where (select true from t2 where t2.id = t1.id); </pre> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
