-1

Using Dbeaver 23.1.0 connected to a Sybase Adaptive Server Database I run 2 simple select queries which both return a record count of 5333 which is expected.

Select * from Availability 
Where ProductId in (select ProductId from Products where SupplierId = 4696)

and

Select count(*) from Availability 
Where ProductId in (select ProductId from Products where SupplierId = 4696)

However, when I run the Delete statement by simply replacing the Select clause, the output says I have modified 18923 records (nearly 4 times what was in the Select statement). I am expecting 5333.

Why?

Delete from Availability 
Where ProductId in (select ProductId from Products where SupplierId = 4696)

enter image description here

I exported the rows to excel using the sql editor from the Select statement and there are only 5333 rows

Additional note .. I see in Dbeaver on the count for the Delete statement it

  • does not - say 18923 records deleted .. but that 18923 are 'modified' .. and the select statement says - fetched - .. that said in the delete statement i am expecting 5333 records modified

Per the user comments there is a Trigger on the table in question: create trigger td_availability on Availability for delete as BEGIN Update Datas..Rates set Active='N' From Datas..Rates xy Join deleted d on xy.RateId = d.AvailabilityId Where d.SourceId = xy.SourceId And d.SourceId > 1 END

user200709
  • 31
  • 4
  • 1
    are there any 'delete' triggers on the table? – markp-fuso Aug 17 '23 at 17:40
  • Yes mate there is: create trigger td_availability on Availability for delete as BEGIN Update Datas..Rates set Active='N' From Datas..Rates xy Join deleted d on xy.RateId = d.AvailabilityId Where d.SourceId = xy.SourceId And d.SourceId > 1 END – user200709 Aug 18 '23 at 04:57
  • if you write a 2nd `select/count` query that joins `Availability` with `Rates` (using the join clauses from the trigger) do you get an additional count that brings your total deleted/modified count up to 18923? if not, check for an 'update' trigger on `Rates` – markp-fuso Aug 18 '23 at 12:14

0 Answers0