delete rows does not equal select count

Question

delete rows does not equal select count

TRACEY-320982

SSChampion

Points: 13653
More actions
April 15, 2009 at 2:41 pm

#130582

select * from
DBO.TRACEY_HOLD_POOL_BASE_ACCT A
INNER JOIN DBO.EXCEL_POOL_BASE_ACCT B ON
A.FY_CD = B.FY_CD AND
A.POOL_NO = B.POOL_NO AND
A.ORG_ID = B.ORG_ID AND
A.ACCT_ID = B.ACCT_ID
(give me 31000 rows)
DELETE FROM DBO.TRACEY_HOLD_POOL_BASE_ACCT
FROM DBO.TRACEY_HOLD_POOL_BASE_ACCT AS spqh
INNER JOIN DBO.EXCEL_pool_base_acct AS sp
ON spqh.FY_CD = sp.FY_CD AND
spqh.POOL_NO = sp.POOL_NO AND
spqh.ORG_ID = sp.ORG_ID AND
spqh.ACCT_ID = sp.ACCT_ID
when delete only get 9000 deleted
Can't figure out what wrong with delete statement.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Brad Mullins-359749 SSC Veteran Points: 214 More actions · Answer 1

TRACEY (4/15/2009)
select * from
DBO.TRACEY_HOLD_POOL_BASE_ACCT A
INNER JOIN DBO.EXCEL_POOL_BASE_ACCT B ON
A.FY_CD = B.FY_CD AND
A.POOL_NO = B.POOL_NO AND
A.ORG_ID = B.ORG_ID AND
A.ACCT_ID = B.ACCT_ID
(give me 31000 rows)
DELETE FROM DBO.TRACEY_HOLD_POOL_BASE_ACCT
FROM DBO.TRACEY_HOLD_POOL_BASE_ACCT AS spqh
INNER JOIN DBO.EXCEL_pool_base_acct AS sp
ON spqh.FY_CD = sp.FY_CD AND
spqh.POOL_NO = sp.POOL_NO AND
spqh.ORG_ID = sp.ORG_ID AND
spqh.ACCT_ID = sp.ACCT_ID
when delete only get 9000 deleted
Can't figure out what wrong with delete statement.

You are only deleting rows from DBO.TRACEY_HOLD_POOL_BASE_ACCT, which is what is reported when you do the delete. Change your select statement to the following and see what you get:

select DISTINCT A.* from

DBO.TRACEY_HOLD_POOL_BASE_ACCT A

INNER JOIN DBO.EXCEL_POOL_BASE_ACCT B ON

A.FY_CD = B.FY_CD AND

A.POOL_NO = B.POOL_NO AND

A.ORG_ID = B.ORG_ID AND

A.ACCT_ID = B.ACCT_ID

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Equals the same number i deleted - thanks i couldn't see this.