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
my table has to be protected from this commands DELETE, DROP, TRANCATE. pls give the solutions.
Give only necessary permission to the user
Another option is to create a view with constant value as one of the columns so that it will not be updated at all
create view myview
as
select columns, 1 as flag from mytable
how to give the permission for the corresponding table pls give some example
Read about GRANT statement in SQL Server help file
Hi.
I’m work c# with sql server 2005, and I have a function Insert in c# but I duplicate the rows in sql.
how a remove in table sql?????
I try remove but is don’t remove.
Help me please
Before inserting data to the table, check for the existance of the value and do insert accordingly
Dear friends
I have to fetch 10 records from one table to another, my que fetches oly the last record can anyone,correct this pls.
alter procedure one_temp2
as
declare @sno numeric(10),@item varchar(40),@description numeric(20),@so1 numeric(10),@so2 numeric(10)
set @so2 = 0
begin
SELECT @so1= max(sno) FROM one
while @so1 > @so2
begin
print @so1
select @sno=sno,@item=item,@description=description from one
insert into two values(@sno,@item,@description)
set @so2=@so2+1
while @so1 = @so2
begin
break
end
end
end
Hi vinoth
U can use cursors to fetch 10 records within stored Precedure.
You dont need a cursor
Use Order by clause also
How to use order by clause for the above
When fetching data, you can use order by clause
But as I said, you dont need a cursor.
Shouldn’t the select in the subquery read SELECT MIN(ID)… instead of SELECT MAX(ID)? The MAX(ID) row is the last duplicate entry, correct? To truly remove duplicates, I would think the MIN is what we need. If I have that backwards, let me know.
Of course, it’s probably a moot point if you’re using an identity ID column. Moot in the sense that the ID column should not have any business purpose.
Once again you amaze me.. This tip is just brilliant – just what I needed – Thanks alot!
hi all
i have four different four table
e.g. emp_name, emp_add, emp_sal, emp_contact
i just want the result in a single row
like
name —– add —— sal —— contact
—————————————————
shiv0——-delhi0—-20000–9876543210
shiv1——-delhi1—-21000–9876543211
shiv2——-delhi2—-22000–9876543212
shiv3——-delhi3—-23000–9876543213
.
.
.
.
so on………..
Can you tell us about the relations between the tables?
i hope this will help you guys
/*
CREATE TABLE [dbo].[userTbl1](
[UName] [nvarchar](50) NULL,
[Email] [nvarchar](250) NOT NULL,
[sex] [nvarchar](20) NULL
) ON [PRIMARY]
GO
*/
/*
insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
*/
/*
select * from userTbl1;
*/
/*
set rowcount 1
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
while @@rowcount > 0
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
set rowcount 0
*/
/*
select * from userTbl1;
*/
This is the most time consuming method. Instead use other approaches
i have table. in that table having 100 records but first 10 records no need to display i want display last 90 records tell me frnds
simple query
if you know exactly 100 records then
SELECT TOP 90 FROM tablename ORDER BY fieldname desc
Harish you can use Row number command as to select particular record set as mentioned below….
with CTE_row_values as(
select field1, field2, row_number() over(order by field1) as row_id from table_name
)
select * from CTE_row_values where row_id between 11 and 100
I want to delete rows from multiple tables using constraint in sql server2005
On delete cascade is one option you need to search for
SELECT distinct Id As Dist_Id,* into Distinct_Tab FROM MyTable
ALTER TABLE Distinct_Tab DROP COLUMN Dist_Id
Insert 100 rows into the following table:
CREATE TABLE #TEST (TEST_ID INT IDENTITY(1,1))
how it posible
Insert into #TEST default values
I think to delete duplicate records and leave only single entry,
set RowCount to 1,then delete query, and finally again set Rowcount=0;by default RowCount=0 to retrieve all Rows
i.e
SET ROWCOUNT 1
delete from Address_Book
where Address_Book_ID
in
(
select Address_Book_ID from Cust_Det where Cust_Acc_No=’90’
)
set RowCount 0
1 This is the most time consuming method
2 Set nocount will not be supported in future release of the SQL Server. Better to avoid it
Thanks
MUCH faster method found:
I tried the method presented in the article [Method 1], and
also searched for alternative methods. When I tried one of the alternatives [Method 2], I found it to be much faster. The query cost was less (21% vs. 79% for each in the batch), but the wall clock time showed a much bigger difference (2 seconds vs. 50 seconds).
They were both run on a subset of real world data with 960,844 rows and 38 duplicates (32 with one dup, 1 with 6 dups). ‘Time’ is a datetime field, while ‘station’ and ‘channel’ are tinyint. The queries were run on a fast dual-processor server.
I am very concerned with speed since I’ll soon be running this on the full data set which has over 1.5 billion records in it.
–[Method 1 (Query cost (relative to the batch): 79%]
–DELETE from #T1
Select ID from #T1
WHERE ID NOT IN
(SELECT MAX(ID) FROM #T1
GROUP BY time, station, channel)
–[Method 2 (Query cost (relative to the batch): 21%] gets all dups, leaves original
–Delete from #T1
Select ID from #T1
where ID <
(Select Max(ID) from #T1 t
where #T1.time = t.time and
#T1.station = t.station and
#T1.channel = t.channel)
Hi all,
follow this one for deleting duplicate rows in a table…
I have dept table
and coumns are deptid,dname
set rowcount 1
delete dept from dept a where(select count(*) from dept b where b.deptid=a.deptid and b.dname=a.dname)>1
while @@rowcount>0
delete dept from dept a where(select count(*) from dept b where b.deptid=a.deptid and b.dname=a.dname)>1
set rowcount 0
This will be a time consuming query. You can use set based method like point 6 specified in this post
You could use DENSE_RANK():
WITH mytableCTE(col1, ranking)
AS
(
SELECT col1, ranking = DENSE_RANK()
OVER(PARTITION BY col1 ORDER BY NEWID() ASC)
FROM mytable WITH (NOLOCK)
)
DELETE FROM mytableCTE WHERE ranking > 1
Thanks so much for this. Saved me hours of work.
DECLARE @T TABLE(ID INT IDENTITY, SName VARCHAR(1000))
INSERT INTO @T SELECT ‘bhoj’
INSERT INTO @T SELECT ‘bhoj’
INSERT INTO @T SELECT ‘raj’
INSERT INTO @T SELECT ‘gopi’
INSERT INTO @T SELECT ‘gopi’
INSERT INTO @T SELECT ‘gopi’
INSERT INTO @T SELECT ‘ruth’
INSERT INTO @T SELECT ‘ruth’
INSERT INTO @T SELECT ‘raj’
INSERT INTO @T SELECT ‘gopi’
–SELECT * FROM @T
DELETE
FROM @T
FROM
@T T
INNER JOIN
(
SELECT ROW_NUMBER() OVER(PARTITION BY Sname ORDER BY SName) DUp,* FROM @t
) Temp ON T.ID = Temp.ID WHERE Dup > 1
SELECT * FROM @T