SQL-Transact Statement - 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

SQL-Transact Statement

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-05-30 : 17:09:55

I need an SQL statement that will allow me to strip out duplicate records based upon a data condition.

Table

PPN_I CPN_I Qty_Required ECN_02
1. ItemA ItemB 1 4506
2. ItemA ItemB 1 3209
3. ItemA ItemB 1 901
4. ItemA ItemD 1 null
5. ItemA ItemD 0 null
6. ItemA ItemF 3 5609
7. ItemA ItemG 1 null
8. ItemB ItemA 1 1725

Okay - I have two conditions I need to enforce

1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:

I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).

So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.

8 records become 4.

Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?

Any assistance would be appreciated,

Thanks in advance.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-30 : 21:19:20

try grouping them according to your duplicate fields, also use having for your where clause for better filter

backup your db before doing this live if you don't have any test database

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 21:26:18

Not sure if got your requirement right. Give this a try.

declare @table table
(
	id		int identity(1,1),
 	PPN_I		varchar(10),
	CPN_I 		varchar(10),
	Qty_Required 	int,
	ECN_02		int
)
insert into @table (PPN_I, CPN_I, Qty_Required, ECN_02)
select 	'ItemA', 'ItemB', 1, 4506	union all
select  'ItemA', 'ItemB', 1, 3209	union all
select  'ItemA', 'ItemB', 1, 901	union all
select  'ItemA', 'ItemD', 1, null	union all
select  'ItemA', 'ItemD', 0, null	union all
select  'ItemA', 'ItemF', 3, 5609	union all
select  'ItemA', 'ItemG', 1, null	union all
select  'ItemB', 'ItemA', 1, 1725

select 	PPN_I, CPN_I, max(ECN_02) as ECN_02
from	@table t
where	(select count(distinct Qty_Required) from @table x 
			where 	x.PPN_I = t.PPN_I
			and	x.CPN_I = t.CPN_I) < 2
group by PPN_I, CPN_I
order by PPN_I, CPN_I

/* RESULT :
PPN_I      CPN_I      ECN_02      
---------- ---------- ----------- 
ItemA      ItemB      4506
ItemA      ItemF      5609
ItemA      ItemG      NULL
ItemB      ItemA      1725

(4 row(s) affected)
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources