Delete duplicate rows 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

General SQL Server Forums

New to SQL Server Programming

Delete duplicate rows with trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-24 : 18:22:49

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
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:09:56

Use this code to delete.

DELETE t
FROM YourTable t
INNER JOIN
(SELECT ord_type ord_no item_no line_seq_no
 FROM YourTable
 WHERE cd_tp IN (1,3)
 GROUP BY ord_type ord_no item_no line_seq_no
 HAVING COUNT(DISTINCT cd_tp)=2)tmp
ON t.ord_type = tmp.ord_type
AND t.ord_no = tmp.ord_no
AND t.item_no = tmp.item_no
AND t.line_seq_no = tmp.line_seq_no
WHERE t.cd_tp=3

b/w can i ask why you want this deletion to be done in trigger?do you want the deletion to be done at the time the record with cd_tp is inserted into table? Then i think you can have an INSTEAD OF INSERT TRIGGER which checks for presence of a record with all the field value for cd_tp=1 and if it exists it wont insert otherwise it will.

something like

CREATE TRIGGER YourTrigger ON YourTable
INSTEAD OF INSERT
AS

IF NOT EXISTS(SELECT 1 FROM YourTable t
          INNER JOIN INSERTED i
          ON t.ord_type = i.ord_type
          AND t.ord_no = i.ord_no
          AND t.item_no = i.item_no
          AND t.line_seq_no = i.line_seq_no
          WHERE t.cd_tp=1
          AND i.cd_tp=3)
INSERT INTO YourTable
SELECT ord_type, ord_no, item_no, line_seq_no, cd_tp
FROM INSERTED
GO

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-25 : 09:20:48

Your delete worked perfectly. Can't tell you how much I appreciate the help.

Thanks a million

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources