Delete duplicate records with trigger

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 2000 Forums

Transact-SQL (2000)

Delete duplicate records with trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-24 : 18:31:58

Have orderrebate table.

ord_type ord_no item_no line_seq_no cd_tp
O 55 Part 1 1
O 55 Part 1 3
O 56 Part4 1 3

When the above records are inserted into orderrebate. After the insert I want to delete line 2. When distinct records come in based on the first 4 fields above I want to delete the on with cd_tp 3 and keep the one with cd_tp 1. If one record comes in with cd_tp 3 I want to keep it. I'm only deleting records with cd_tp 3 if they have a corresponding record with a cd_tp 1.

Nothing seems to work. This is my last hurdle in getting my program complete and I hitting a wall. Any help would be greatly appreciated.

Here is one of many examples I've tried with no luck:


delete OrderRebate
FROM OrderRebate
inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
and exists 
(
	select ord_type, ord_no, item_no, line_seq_no 
	from OrderRebate
	where 
		inserted.ord_type = ord_type and
		inserted.ord_no = item_no and
		inserted.ord_no = ord_no and inserted.line_seq_no = line_seq_no
	group by ord_type, ord_no, item_no, line_seq_no 
	having count(*)>1
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 18:34:55

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105480

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources