Duplicate records removal on Insert

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)

Duplicate records removal on Insert

Author

Topic

girims
Starting Member

5 Posts

Posted - 2004-11-18 : 18:15:19

I have a table which is automatically updated from UPS system. Lineid 28 was avlid entry. However the user voided the Label in the UPS system and it recorded another entry under void column with a 'Y' refer lineid 29. Now the lineid 28 and 29 need to removed, because the user recreated the label again .... refer 30 and 31 which are valid and all i want to have in database.

Any help in this regard will be appreciated.

lineid control box tracking void
28 265328 1 1Z8AT1110345814508 N
29 265328 1 1Z8AT1110345814508 Y
30 265328 2 1Z8AT1110344064919 N
31 265328 2 1Z8AT1110346028722 N

Thanks,

Giri

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 18:48:00

if you need delete command.
Then here it is.

Delete from YourTableNameHere Where lineid=28 or lineid=29.

If you want something else, then tell us on what basis you want to delete record.

cheers

mk_garg

girims
Starting Member

5 Posts

Posted - 2004-11-18 : 18:53:34

i am aware of the delete command. I want this to automatically happen on Insert of new records.

Giri

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 19:21:07

You have to write a trigger for that.
It should be like this.

CREATE TRIGGER YourTableName_trig1 ON YourTableName
FOR INSERT
AS
declare @control as int
declare @box as int
declare @tracking as varchar(30)
declare @void as char(1)

select @control=control,@box=box,@tracking=tracking,@void = void from inserted
if @void='y'
delete from YourTableName
where control=@control and box=@box and tracking=@tracking

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-19 : 02:10:15

can you handle this in your application instead of having the trigger do it for you?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources