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.
| Author |
Topic |
|
shajimanjeri
Posting Yak Master
179 Posts |
 Posted - 2007-07-24 : 02:52:43
|
| Hi All,I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar](2)-ReceivedTime [datetime](3)-Status [char].Now here how to remove duplicate entry of same mobile number to MobileNumber field?RegardsShaji |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-24 : 03:00:49
|
SQL Server 2000:DELETE s1 FROM C1_Subscribers AS s1 INNER JOIN C1_Subscribers AS s2 ON s2.MobileNumber = s1.MobileNumber AND s2.ReceivedTime < s1.ReceivedTime SQL Server 2005:DELETE xFROM ( SELECT ROW_NUMBER() OVER (PARTITION BY MobileNumber ORDER BY ReceivedTime DESC) AS RecID FROM C1_Subscribers ) AS x WHERE RecID > 1 Peter LarssonHelsingborg, Sweden |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-24 : 03:01:37
|
| It all depends which record you want to keep, according to either ReceivedTime or Status, or both.Peter LarssonHelsingborg, Sweden |
|
|
|
shajimanjeri
Posting Yak Master
179 Posts |
Posted - 2007-07-24 : 03:31:55
|
| Thanks Peter Larsson,But I need to avoid duplicate entry of mobilenumber. Here I mean how to check whether the entering mobile number is already exist or not. If not exist in the table then add it or if exist then avoid duplicate entry.Is there SQL statement?Thanks in advance |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-24 : 04:07:55
|
| IF NOT EXISTS (SELECT * FROM C1_Subscribers WHERE MobileNumber = @Param1) INSERT C1_Subscribers (MobileNumber, ReceivedDate, Status) VALUES (@Param1, @Param2, @Param3)Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
Duplicate entry