problem in deleting the replicated date in temp ta

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

Transact-SQL (2005)

problem in deleting the replicated date in temp ta

Author

Topic

mmsql
Starting Member

1 Post

Posted - 2010-10-04 : 11:44:48

Dear All,
I have a problem. I need to eliminate the multiple records[replicated] from a table leaving original one record.
Ex:
uid EventID EventName
1 001 abc
2 002 xyz
3 001 abc
4 001 abc

Result:
uid EventID EventName
1 001 abc
2 002 xyz

DECLARE @tblev_event Table
(
UID INT NULL
,EVENT_DATETIME DATETIME NULL
,EVENT_SUMMARY NTEXT NULL
,IS_IDP BIT NULL
,EVENT_DOMAIN_UID INT NULL
,EVENT_STATE_UID INT NULL
,EVENT_LOCALITY_UID INT NULL
,EVENT_LOCATION_UID INT NULL
,EVENT_AREA_UID INT NULL
,EVENT_STATUS BIT NULL
,MissionID INT NULL
) -- Temp Table

/*Remove the Duplicate Entries */
DELETE FROM @tblev_event WHERE UID IN
(SELECT MAX(UID) FROM @tblev_event
GROUP BY UID HAVING COUNT(UID) > 1)
SET @int_Status = 3

select @int_Status = count (*) from @tblev_event

/*Select the Result*/
SELECT
obj@tblev_event.UID
,obj@tblev_event.EVENT_DATETIME
,obj@tblev_event.EVENT_SUMMARY
,obj@tblev_event.IS_IDP
,obj@tblev_event.EVENT_DOMAIN_UID
,obj@tblev_event.EVENT_STATE_UID
,obj@tblev_event.EVENT_LOCALITY_UID
,obj@tblev_event.EVENT_LOCATION_UID
,obj@tblev_event.EVENT_AREA_UID
,obj@tblev_event.EVENT_STATUS
,obj@tblev_event.MissionID
FROM @tblev_event as obj@tblev_event

The problem in this SP, it deletes all the replicated record not leaving the original.Ex:
uid EventID EventName
1 001 abc
2 002 xyz
3 001 abc
4 001 abc

Result:
uid EventID EventName
2 002 xyz
Note:
1 001 abc -- is also deleted not leaving the original.

I can figure it out some thing has to appended in where clause of the delete query, I tried lot,Unfortuntely I cant attain.
Can anyone help me to sort this issue, Please.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-04 : 12:06:22

Does the table you are deleting from not have a primary key?

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 12:13:28

see 6th point here

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources