Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]
Reference : Pinal Dave (https://blog.sqlauthority.com)
450 Comments. Leave new
hiii
i want to select the second highest salary from an Employee table!
how to do that!
Thanks!
Hi, please help me…. I couldn’t find answer for this anywhere…
I have a table with a column having data type varchar and it has data with numbers and alphabets in it. I want to replace the data in the column with ‘ ‘ where there are alphabets.
(REPLACE((column),’S’,”)) — I know this would replace s with ‘ ‘, but how to replace all the alphabets with ‘ ‘.
Please answer, I urgently need the answer.
Thanks to all in advance….
Hellooo
I have a table for example EMployees, it has a PID which is auto number, and other 2 fields one EmployeeID and other is Name.
what i want is to delete duplicated data in Employee ID and Name for sure, what to do plz….
Thanks
DELETE
FROM EMployees
WHERE Employee ID IN
(select Employee ID
from EMployees
group by EMployees
having count(EMployees) > 1)
just try this……;
can any budy explain trigger concept…
this question i have asked in one of my interview, and requirement is like delete record should be in single query its ok if you can write subquery, but without use of temp table, without use cursor, and without use of insert identity column. still i am waiting for the answer,
Pls give solution as in sql server 2000
Hi
I am having a problem in generating reports from two SQL tables.
eg.
table1.
loc1 10
loc2 10
loc3 40
Table2
loc1 xx
loc1 yy
loc1 zz
loc3 pp
I want to print.
Table1 + table2
loc1 10 loc1 xx
null loc1 yy
null loc1 zz
loc2 10 null
loc3 40 loc3 pp
Any way to generate this using joins or any other.Pl. advise
pankaj
U are doing a great Job…
All the Best all Of u…
i like this site
thanks
Great solution, however….i have a table that has…………..
1] Duplicate Id’s
2] Unique id’s
Now on the basis of your query i would be able to delete the duplicate id’s(and records corresponding to that is)..but that would delete all subsequent unique id’s as well.
So basically i wanto find all duplicates, choose one, delete the rest and keep the unique records as well.
Could you or anyone please help me find a solution to this…
Thanks!
Marvellous men!!!!!!!!1
Hi ,
Pls help in this issue .
create table test1
( A int , B varchar(2) , C varchar(20) , D int , E varchar(20))
insert into test1 values (1,’A’,’aaa’ , 111, ‘adf’)
insert into test1 values (2,’B’,’bbb’ , 222, ‘adsf’)
insert into test1 values (3,’C’,’ccc’ , 333, ‘cfe’)
insert into test1 values (1,’A’,’aaa’ , 444, ‘vgd’)
insert into test1 values (4,’D’,’ddd’ , 555, ‘qef’)
Now for the above table Columns A , B and C (combined) form the unique identifiers for a given record.
I would like a query to retrieve the unique records(identified by columns A , B and C in the table )
i.e
2,’B’,’bbb’ , 222, ‘adsf’
3,’C’,’ccc’ , 333, ‘cfe’
4,’D’,’ddd’ , 555, ‘qef’
Constraint being that i cannot add any composite / identity for the three columns (A,B ,C)
Any help is highly appreciated……
select t1.* from table as t1 inner join
(
select a,b,c from table
group by a,b,c
having count(*)=1
) as t2
Plz give the int size and u will try and get a success.
u r give a char size but u don’t give a int size that u give a int size dear….
its really nice
regds
faisal qureshi
My solution is:
select * from DUPLICATE
union
select * from DUPLICATE
This query will filter out all duplicate records from DUPLICATE table
Tarun
But this might be more effecient
select distinct * from DUPLICATE
If you use version 2005 or above, try method 6
71.
Hi, please help me…. I couldn’t find answer for this anywhere…
I have a table with a column having data type varchar and it has data with numbers and alphabets in it. I want to replace the data in the column with ‘ ‘ where there are alphabets.
(REPLACE((column),’S’,”)) — I know this would replace s with ‘ ‘, but how to replace all the alphabets with ‘ ‘.
Please answer, I urgently need the answer.
The Answer is as follow :
You have to use Stuff function in place of Replace.
e.g.
select stuff(column,1,len(column),”) from Table_name
A.K.Rastogi
If your requirement is to extract only numbers from the text, try using the following method
This soluton worked perfectly in my situation! I had read many other suggestions online but most of them were overly complicated and usually required creating additional tables. Thanks for posting this!
Hi Pradeep,
Today I saw your question. It’s late, but if you haven’t find any solution below may help…
____________________________________________________________________________________________
SELECT * FROM test1 t1, (SELECT A, B, C FROM test1 GROUP BY A,B,C HAVING COUNT(1)>1) t2
WHERE t1.A t2.A AND t1.B t2.B AND t1.C t2.C
____________________________________________________________________________________________
Regards
Saroop
This will throw error
See my reply to the original poster
____________________________________________________________________________________________
SELECT * FROM test1 t1, (SELECT A, B, C FROM test1 GROUP BY A,B,C HAVING COUNT(1)>1) t2
WHERE t1.A <> t2.A AND t1.B <> t2.B AND t1.C <> t2.C
____________________________________________________________________________________________
thanks satish
tahnks ravi
cool !!!!!!!
hi dave,
i m really imprssd with ur blog. n u knw d bst part is u rply to each possible post.
hoping to get best out dis blog
thanks
and all d best
deepti
Hi all,
i have doupt ..plz clarify that.
create table a(empid int)
create table b(empid1 int)
insert into a values(1)
insert into b values(1)
insert into a values(2)
insert into b values(3)
output:
empid
———–
1
2
empid1
———–
1
3
with out using temporary table to del the comman record from separate two table.