Re: [sqr-users] Select statement returns far fewer rows thandelete statement using the same criteria

["Larry Roux" <LRoux@syr.edu>]

Try fully qualifying the exists statement?  ie :


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

also,  PS_OPEN_TRANS wouldn't happen to be a view, would it?  I was
thinking if it was the data beneath it could be changing as you do the
deletes.  





Larry Roux
Information Systems
Syracuse University
lroux@syr.edu


>>> jej1216@hotmail.com 1/18/2007 4:14 PM >>>
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

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