> 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???
>
> 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
Try the following steps:
1) select count(*) from table2
2) select count(*)
from table2 t2
where exists(select * from table1 t1
where t2.form_id = t1.form_id
and t1.form_filename_src containing 'hello')
3) delete from table2 t2
where exists(select * from table1 t1
where t2.form_id = t1.form_id
and t1.form_filename_src containing 'hello')
Now, a new 'select count(*) from table2' should return the result of step 1
minus the result of step 2.
What can be the reason for you observing more rows deleted than you expected,
might be that your select would delete both rows if there were one record
matching the table1 requirements and another record not matching the table1
requirements, but with the same subform_id. The delete I've written above would
only delete the one row that matched (so it is not quite the same query as your
original query, it depends on your requirements which of them is correct). If
your original query is the correct one, then change step 2 and 3 to contain:
where exists(select * from table1 t1
join table2 t2b on t1.form_id = t2b.form_id
where t2.subform_id = t2b.subform_id
and t1.form_filename_src containing 'hello')
HTH,
Set
