From: "Bart Smissaert" <bart.smissa...@gmail.com> Then the SQL I was trying to improve:
DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE JULIANDAY(TABLE2.START_DATE, '-14 month') > JULIANDAY(TABLE1.START_DATE) AND TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) ----- To my understanding, SQLite will not use indices on function results. I wonder if you would have more luck (since START_DATE is yyyy-mm-dd) using DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = TABLE2.PATIENT_ID ) I believe this will make use of a joint index on PATIENT_ID and START_DATE, with the preferred order depending on which is the more restrictive term (I'd guess best would be and index on (PATIENT_ID, START_DATE)). Jon Dixon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users