firebird 2.5.0.26074 on windows 7 home premium x64 Below is a (manually obscured, simplified table) scenario that (hopefully) matches what I'm doing, with actual return count values.
Is my SQL just woefully incorrect, or am I possibly experiencing a problem with fb 2.5, in apparently seeing a great many more records deleted than I expect, based on the various queries. I expected the last query to yield (123686 - 7617) = 116069, not the reported 107548. Why didn't it??? data content notes: 1)all form_id values in table1 are unique 2)all subform_id values in table2 are unique EXCEPT there are many duplicates of '0' (zero), _and_, some of those _are_ matched to table1 entities that are like '%hello%' (i.e. t1.form_id = t2.form_id and t2.subform_id = 0) - I don't currently know counts as I've been waiting for an ISQL query (because I want to see what I entered) I did botch, to finish (and Ctrl-C didn't interrupt it.) I will attempt to provide additional information, if what I have presented is insufficient for analysis. TIA for any assistance. ============================================== create table table1 (form_id as Numeric ,form_filename_src varchar(256) ) ; create index idx_table1 on table1(form_id) ; create table table2 (subform_id as numeric ,form_id as numeric ) ; create index idxA_table2 on table2(form_id) ; create index idxB_table2 on table2(subform_id) ; select count(form_filename_src) from table1 where form_filename_src like '%hello%' ; 7617 select count(subform_id) from (select t2.subform_id from table2 t2, table1 t1 where t1.form_id = t2.form_id and t1.form_filename_src like '%hello%') ; 7617 select count(*) from table2 ; 123686 delete from table2 where subform_id in (select t2.subform_id from table2 t2, table1 t1 where t2.form_id = t1.form_id and t1.form_filename_src like '%hello%') ; (no delete count or rows affected returned) select count(*) from table2 ; 107548 ===================
