-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Ritesh_Kesharwani
Message 3 in Discussion
Hi Neeraj
Please prefer this
How can I delete the duplicate records?
You can usually solve a problem in several ways with T-SQL, and the problem
of deleting duplicate records is no exception. Listing 1
</Files/09/21704/Listing_01.txt> shows one solution that you might find
helpful. This reasonably complex T-SQL query uses a correlated subquery.
Correlated subqueries are a little bit like nested loops. Physical execution
plans might be different from the following logical description of the
query, but you can interpret this query in the following way:
* For each row of the dups table, run an existence check.
* Select the FirstName, LastName, and NameId columns from the dups
table in the outer query.
* The existence check is true if the correlated subquery (which is the
inner query) returns any rows--in other words, if the row that the outer
query is processing is duplicated in the table.
* Delete the current row from dups if the existence check was true.
When the code checks the first row in Table 1, which has NameId 106, the
EXISTS check returns false because the inner query doesn't return any rows.
Yes, rows match based on FirstName and LastName, but none of the matching
rows have a NameId less than 106. So, the code doesn't delete this row. Now,
assume the current row of the outer query is the row with NameId 123. The
EXISTS check returns true for this row because the inner query returns a row
that matches FirstName and LastName and also has a NameId that is less than
123. Which row causes this match? The row with NameId 106 has the same
FirstName and LastName as the row with NameId 123. And DupsInner.NameId 106
(which is the alias I use for the table in the inner query in Listing 1) is
less than the NameId value of 123 that the code passed in from the outer
query.
Correlated subqueries and existence checks can be confusing to the T-SQL
novice. But they can be powerful, and learning to use them is worth the
effort.
CREATE TABLE Dups (NameId int, FirstName varchar(10), LastName varchar(10))
GO INSERT INTO Dups VALUES (106, 'Robert', 'Aide') INSERT INTO Dups VALUES
(107, 'David', 'Koidl') INSERT INTO Dups VALUES (108, 'Hongbo', 'Li') INSERT
INTO Dups VALUES (123, 'Robert', 'Aide') INSERT INTO Dups VALUES (124,
'David', 'Koidl') INSERT INTO Dups VALUES (125, 'Hongbo', 'Li') INSERT INTO
Dups VALUES (138, 'Robert', 'Aide') INSERT INTO Dups VALUES (139, 'David',
'Koidl') INSERT INTO Dups VALUES (140, 'Hongbo', 'Li') INSERT INTO Dups
VALUES (153, 'Robert', 'Aide') INSERT INTO Dups VALUES (154, 'David',
'Koidl') INSERT INTO Dups VALUES (155, 'Hongbo', 'Li') GO
DELETE FROM dups WHERE EXISTS ( SELECT NameId FROM dups DupsInner WHERE
DupsInner.FirstName = Dups.FirstName AND DupsInner.LastName = Dups.LastName
AND DupsInner.NameId < Dups.NameId )
Ritesh Kesharwani
SQL Star Intlernational Ltd.
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.Riteshk.blogspot.com
Cell - 9849976150
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
