Removing rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing rows

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-07-02 : 09:31:11

Hi Guys,

I have just completed a query and I have realized that they are duplicates with cardnumbers because they are ppl in the database that are recorded as having both a credit card and then not having a credit card. Let me explain...

-If a person appears twice in the result set that is one row as the person card number along with a 'Y' and the below it the same person appears again with the same card number,first name but the difference is that they have a 'N' in the credit card field.In the final result set I want them to appear with a 'Y'

-If a person appears once with a 'Y' then the result should appear with a 'Y'

-If a person appears once with a 'N' the result should appear with a 'N'

I would like to delete those rows that has 'no' and keep the ones that 'yes'. How would I go about doing that with out deleting the person completely.

sample of present results.


FN      MN      LN              CreditCard?   CardNumber
ANNE	M	ABOAB		N                1
TERROL	W	ALLEYNE		N                2
TERROL	W	ALLEYNE		Y                2
WENDY	D	ALLEYNE		Y                3
RICHARD	D	ALLSOPP		N                4
JOHN	NULL	BEALE		N                5
JOHN	NULL	BEALE		Y                5

sample of how I would like results to look.


FN      MN      LN              CreditCard?   CardNumber
ANNE	M	ABOAB		N                  1
TERROL	W	ALLEYNE		Y                  2
WENDY	D	ALLEYNE		Y                  3
RICHARD	D	ALLSOPP		N                  4
JOHN	NULL	BEALE		Y                  5

Initial Query


select distinct c.cardnumber
	,FirstName
	,InitialsName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,HomePhone
	,CellPhone
	,WorkPhone
	,Gender
	,CycleCode
	,CardClass
	,c.ClassCode
	,IsCreditCardKey  = case 
		when (IsCreditCardKey = 'yes' then 'Y'
		else 'N'
		end 
	
	
from customer c inner join AlternateAccessKeys a on a.cardnumber=c.cardnumber
	inner join SubsidiaryByCustomerByClassTransactionDetail s on s.cardnumber=c.cardnumber
where s.subsidiaryid in (select subsidiaryid
		     from subsidiary
		     where subsidiaryid in (1411,1419)
		     )
group by  c.cardnumber
	,FirstName
	,InitialsName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,HomePhone
	,CellPhone
	,WorkPhone
	,Gender
	,CycleCode
	,CardClass
	,c.ClassCode
	,IsCreditCardKey

order by c.cardnumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 13:36:46

[code]DELETE t
FROM Table t
INNER JOIN (SELECT FN,MN,LN,CardNumber
FROM Table
GROUP BY FN,MN,LN,CardNumber
HAVING COUNT(DISTINCT CreditCard)=2)t1
ON t1.FN=t.FN
AND t1.MN=t.MN
AND t1.LN=t.LN
AND t1.CardNumber=t.CardNumber
AND t.CreditCard='N'[/code]

suggest you first use select instead of DELETE to check if you're getting correct records which are to be removed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources