[sqr-users] Select statement returns far fewer rows than deletestatement using the same criteria

["Joe" <jej1216@hotmail.com>]

I have an SQL question, but this group has proven to be such a goldmine of help 
for SQR, I wanted to post this here as well as in a SQL Server group.

I have a select statement that returns 36 rows.  I changed it to a delete 
statement by simply replacing 'Select * from' with 'Delete from.'  The delete 
statement deletes 191 rows.

I would think that, regardless of whether or not the criteria is correct, both 
the 'select' and the 'delete' would hit the same number of rows.

Here is my select:

SELECT * 
FROM PS_COST C
WHERE C.ASSET_ID      = '000000000315' 
  AND C.BUSINESS_UNIT = '00010'
  AND EXISTS (SELECT * FROM PS_OPEN_TRANS OT 
               WHERE OT.CALC_DEPR_STATUS = 'P' 
                 AND OT.DTTM_STAMP    = C.DTTM_STAMP
                 AND OT.TRANS_TYPE    = C.TRANS_TYPE
                 AND OT.ASSET_ID      = C.ASSET_ID
                 AND OT.BUSINESS_UNIT = C.BUSINESS_UNIT
                 AND OT.BOOK          = C.BOOK)  

And here is my delete:

DELETE FROM PS_COST
WHERE ASSET_ID      = '000000000315' 
  AND BUSINESS_UNIT = '00010'
  AND EXISTS (SELECT * FROM PS_OPEN_TRANS OT 
               WHERE OT.CALC_DEPR_STATUS = 'P' 
                 AND OT.DTTM_STAMP    = DTTM_STAMP
                 AND OT.TRANS_TYPE    = TRANS_TYPE
                 AND OT.ASSET_ID      = ASSET_ID
                 AND OT.BUSINESS_UNIT = BUSINESS_UNIT
                 AND OT.BOOK          = BOOK)  

Any help would be appreciated.

- Joe

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users