Delete Duplicate Records from table which does not have primary Key

Hello friends
Greetings !

I will share an interesting Query that Delete records from table which doesn't have any Key (primary Key,Unique Key ).

Step 1 : Create a table named as empName
run the following query
CREATE TABLE [dbo].[emp](
[empId] [int] ,
[empName] [nvarchar](max) NULL
)

Step 2 : Insert records into the table empName

INSERT INTO [emp] VALUES (1,'Shubhank')
INSERT INTO [emp] VALUES (2,'Viru')
INSERT INTO [emp] VALUES (3,'Shubhank')
INSERT INTO [emp] VALUES (4,'Maths')
INSERT INTO [emp] VALUES (5,'Shubhank')
INSERT INTO [emp] VALUES (6,'Shubhank')
INSERT INTO [emp] VALUES (7,'Denis')
INSERT INTO [emp] VALUES (8,'Gates')
INSERT INTO [emp] VALUES (9,'Linux')
INSERT INTO [emp] VALUES (10,'Shubhank')
INSERT INTO [emp] VALUES (11,'Gary')
INSERT INTO [emp] VALUES (12,'Rajkishan')
INSERT INTO [emp] VALUES (13,'Shubhank')
INSERT INTO [emp] VALUES (14,'Rajiv')
INSERT INTO [emp] VALUES (15,'Mak')
INSERT INTO [emp] VALUES (16,'Gary')
INSERT INTO [emp] VALUES (17,'Raj')

step 3: Now in that table there are so many duplicated records of name 'Shubhank' having 6 records , and there is no primary key ,
then how can you delete only two records having with name 'shubhank' out of six.

Step 4 : Here is the Query run it

DELETE TOP(2) from emp Where
empName LIKE 'Shubhank%'

Step 5 : Now run the Query
SELECT COUNT(*) FROM emp Where
empName LIKE 'Shubhank%'

you will get four in result set.

Thanks
Shubhank Upadhyay

Comments

Post a Comment

Popular posts from this blog

String Function in SQL-Substring,Reverse,CharIndex

Suppose You have String "~/ABC/PQR.xxx" in your tables Now u Want to string PQR.xxx fromm above string So For That Follow the Steps 1) Open NewQuery Window 2) Paste following Code declare @str as varchar(200),@str1 as varchar(200),@rev as varchar(200) set @str ='~/ABC/PQR.xxx' select @rev = reverse(@str) SELECT @str1 = CHARINDEX('/', reverse(@str)) select @str1,@rev'@rev',reverse(substring(@rev,0,convert(int,@str1))) 3) Execute the Code 4) You will get the Output Like that PQR.xxx
Read more

Difference Between ArrayList and Generic.List C#/VB.Net

Hi, Last few Days, I learnt a very basic and important thing about  that ,When one should go for ArrayList or Generic.List in coding......On basis of that, I found  few difference between both. ArrayList Its comes under System.Collections namespace. Its contains Array of Object.i.e Any objects type we can store in a ArrayList.We can store any no. of objects at runtime. ArrayList aryList= new ArrayList(); // No Need to specify the object type,can store anything arList.Add(1); arList.Add("Shubhank"); arList.Add("False"); We can store any objects in ArrayList and can retrieve only Objects from ArrayList... We can say that ArrayList is not Type-Safe .At the Time of inserting recods ,we can store any type of objects,but at the time of fetching we can't be sure that  Values  which are populate by arList (ArrayList object ) of what type..however.. we have to following code while fetching records/items from Array list foreach(Object o in...
Read more

How to Use Not IN operator

Hello Friends Here i am explaining an example of NOT IN operator. Suppose you have two tables one is employee and Bademployee employee and Bademployee has relationship. Bademployee contains records who are bad Now you want to show the Good employee so following is the example Step : 1 create Table emp Run this Query in your Query Analyzer for table emp 1) CREATE TABLE [dbo].[emp]( [empId] [int] NOT NULL, [empName] [nvarchar](max) NULL, CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ( [empId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Now Run this Query in your Query Analyzer for table bademp CREATE TABLE [dbo].[bademp]( [empid] [int] NOT NULL, [bademp] [nvarchar](50) NOT NULL, CONSTRAINT [PK_bademp] PRIMARY KEY CLUSTERED ( [empid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY...
Read more