Delete where COUNT(*) > 1 / Weird Delete

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

SQL Server 2008 Forums

Transact-SQL (2008)

Delete where COUNT(*) > 1 / Weird Delete

Author

Topic

Balance
Starting Member

24 Posts

Posted - 2011-04-13 : 17:02:19

Based on the sample data below I would like to only keep those records that are associated with CMT_TYPES = 'OFFICER' *only* if there are multiple rows per CUST_ID. In other words, if a CUST_ID has only 1 row with CMT_TYPES = 'CHARTER' I would like to keep it, but if CUST_ID = 1004116 has 2 rows, one with CMT_TYPES = 'CHARTER' and another CMT_TYPES = 'OFFICER', then I only want to keep the latter. I can't get my head around this DELETE statement, so I'm hoping someone can help me out.

comm_ID CMT_TYPES CUST_ID
------------ ----------------------- -------------------------
713279 CHARTER 1004116
723092 OFFICER 1004116

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 17:24:26

Can you provide more samples please


713279	CHARTER	1004116	1
723095	CHARTER	1004116	2
723092	OFFICER	1004116	1
723093	OFFICER	1004116	2
723094	OFFICER	1004116	3

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 17:27:36


declare @comm TABLE(comm_ID INT, CMT_TYPES nvarchar(50), CUST_ID INT)

insert into @comm
SELECT 713279, 'CHARTER', 1004116 
UNION
SELECT 723092, 'OFFICER', 1004116
UNION
SELECT 723093, 'OFFICER', 1004116
UNION
SELECT 723094, 'OFFICER', 1004116
UNION
SELECT 723095, 'CHARTER', 1004116

DELETE FROM @comm
WHERE comm_ID IN (
SELECT comm_ID FROM
(
SELECT *, 
       ROW_NUMBER() OVER(PARTITION BY CMT_TYPES, CUST_ID ORDER BY CUST_ID DESC) AS RowNum 
 FROM @comm) a 
 where RowNum > 1
 )
 
 select * From @comm

If you don't have the passion to help people, you have no passion

Balance
Starting Member

24 Posts

Posted - 2011-04-13 : 18:07:09

yosiasz,

713279 CHARTER 1004116 1
723095 CHARTER 1004116 2
723092 OFFICER 1004116 1
723093 OFFICER 1004116 2
723094 OFFICER 1004116 3

Should result in just this row:

723094 OFFICER 1004116 3

However, if the sample data only had:
723095 CHARTER 1004116 2

Then, it would keep that row.

In other words, if there's more than one row with multiple CMT_TYPES (charter, officer), I only want to keep the officer one.

Thank you!

P.S.
Love your signature

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-04-14 : 11:46:36

slight modification to yosiasz code:



declare @comm TABLE(comm_ID INT, CMT_TYPES nvarchar(50), CUST_ID INT)

insert into @comm
SELECT 713279, 'CHARTER', 1004116 
UNION
SELECT 723092, 'OFFICER', 1004116
UNION
SELECT 723093, 'OFFICER', 1004116
UNION
SELECT 723094, 'OFFICER', 1004116
UNION
SELECT 723095, 'CHARTER', 1004116

DELETE 
	x
FROM
	@comm x
INNER JOIN
	(
	SELECT 
		comm_ID, CMT_TYPES, CUST_ID
		  ,ROW_NUMBER() OVER(PARTITION BY CUST_ID ORDER BY CMT_TYPES desc, comm_ID desc) AS RowNum 
	 FROM @comm
	) a 
ON
	x.comm_ID = a.comm_ID 
WHERE 
	a.RowNum > 1
 
 
 SELECT * FROM @comm

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Balance
Starting Member

24 Posts

Posted - 2011-04-14 : 15:52:32

This just seems to delete dupes (based on cust_id), which is not what I'm after.

Specifically...

713279 CHARTER 1004116 1
723095 CHARTER 1004116 2
723092 OFFICER 1004116 1
723093 OFFICER 1004116 2
723094 OFFICER 1004116 3

Should result in just this row:

723094 OFFICER 1004116 3

However, if the sample data had:
723095 CHARTER 1004116 2
723099 BLEH 1004116 2

Then, it would keep the 723099 row.

The key requirement is that if one of the rows has CMT_TYPES = 'OFFICER', that I must keep that row (grouped by CUST_ID).

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-04-15 : 14:21:58

Ok, here is a HORRIBLE kludge. Maybe super Peter or Rob can clean it up...

declare @comm TABLE(comm_ID INT, CMT_TYPES nvarchar(50), CUST_ID INT)

insert into @comm
SELECT 713279, 'CHARTER', 1004116 
UNION
SELECT 723092, 'OFFICER', 1004116
UNION
SELECT 723093, 'OFFICER', 1004116
UNION
SELECT 723094, 'OFFICER', 1004116
UNION
SELECT 723095, 'CHARTER', 1004116
UNION
SELECT 723095, 'CHARTER' ,1004116 
UNION
SELECT 723099, 'BLEH' ,1004116 
UNION
SELECT 723100, 'OFFICER', 1004117
UNION
SELECT 723101, 'CHARTER', 1004117
UNION
SELECT 723102, 'CHARTER' ,1004118 
UNION
SELECT 723103, 'BLEH' ,1004118 


declare @keep table (comm_ID int, CUST_ID int)

insert into @keep (comm_ID, CUST_ID)
select 
	max(comm_ID),CUST_ID 
from
(SELECT 
		comm_ID, CMT_TYPES, CUST_ID		
		,case CMT_TYPES when 'OFFICER' then 1 else 0 END as [Keeper]
FROM 
	@comm) x
where 
	x.Keeper = 1
group by
	CUST_ID 



insert into @keep 
select 
	max(comm_ID),CUST_ID 
from
(SELECT 
		comm_ID, CMT_TYPES, CUST_ID			
		,case CMT_TYPES when 'OFFICER' then 1 else 0 END as [Keeper]
FROM 
	@comm) x
where 
	x.Keeper = 0
and
	x.CUST_ID  not in
(select CUST_ID  from @keep )
group by
	CUST_ID 

DELETE
	x
FROM
	@comm x
LEFT JOIN
	@keep k
ON
	x.comm_ID = k.comm_ID 
WHERE
	k.comm_ID IS NULL 

select * from @comm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources