hmm, also appears the original post didn't quite spell it all out (I know I typed it somewhere...)
On the delete I have 107548 rows remaining. I expected, and in the later staged version got, 123686 - 7617 = 116069 rows remaining. Why the discrepancy in the original database? --- In [email protected], "learntrade" <learntrade@...> wrote: > > 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 > > =================== >
