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 firebird-support@yahoogroups.com, "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
>
> ===================
>