Answer / swati tripathi

declare myCursor cursor for
select empid
from employee
group by empid
having count(*) > 1

declare @EmpId int


OPEN myCursor;
FETCH NEXT FROM myCursor INTO @EmpId
WHILE @@FETCH_STATUS = 0
BEGIN

delete top(select count(*)-1 from employee where
empid=@EmpId) from employee where empid=@EmpId

FETCH NEXT FROM myCursor INTO @EmpId
END
close myCursor

deallocate myCursor

Answer / sumathy

Execute the following query for each duplicate value.

1.Delete duplicate of empname='A'

delete top(select count(*)-1 from employee where
empname='A') from employee where empname='A'

2.Delete duplicate of empname='D'

delete top(select count(*)-1 from employee where
empname='D') from employee where empname='D'

Answer / navneet sharma

select distinct * from tablename

Answer / honey

with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >1

Answer / sunil

set rowcount 1 -- set row count 1

delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1

while @@rowcount > 0
begin
delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1
end
set rowcount 0

Answer / pradeep

DECLARE @TEMP_TABLE TABLE (EMPNAME VARCHAR(10),SALARY
VARCHAR(10))

INSERT INTO @TEMP_TABLE (EMPNAME ,SALARY )
SELECT 'A','200' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','400' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'B','300'

/* TABLE RAW DATA */
SELECT * FROM @TEMP_TABLE

SELECT * FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1

/* OUTPUT SHOULD BE */
SELECT DISTINCT * FROM @TEMP_TABLE

/* DELETE DUPLICATE/TRIPLICATE.... RECORDS */
SET ROWCOUNT 1

DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1
WHILE @@ROWCOUNT > 0
DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1

SET ROWCOUNT 0
/**********************************************/
/* EXPECTED OUTPUT TABLE */
SELECT * FROM @TEMP_TABLE

Answer / shankaranarayanan v

while exists(select count(*) from employee group by empname having count(*)>1)
begin

delete top(1) from employee where empname in
(
select min(empname) as deletedname
from employee
group by empname
having count(*)>1
)

end

Answer / devender kumar

This query is for sql server 2005 and higher version of sql
server. It will not run on older versions.



with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >3

Answer / gaurav sharma

SELECT DISTINCT * INTO Employee1 FROM Employee
DROP Table Employee
sp_rename 'Employee1','Employee'

Answer / samba shiva reddy . m

delclare @temp(empname varchar(50),sal int)
select * into @temp
FROM employee
GROUP BY empname, salary
HAVING count(*) > 1
delete from employee
select * into employee from @temp as temp

What is extended stored procedures?

0 Answers  

What is store procedure? How do they work? When do you use?

0 Answers  

What languages bi uses to achieve the goal?

0 Answers  

How to create stored procedures with parameters in ms sql server?

0 Answers  

What types of Joins are possible with Sql Server?

0 Answers   NA,

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

1 Answers   HCL,

What is the difference between executequery () and executeupdate ()?

0 Answers  

What is the default server name for sql server?

0 Answers  

How do you test your database? : sql server database administration

0 Answers  

Hi Friends, I have a table in which there are thousands of records and in city field there is NULL value for all records now i want to change that null value with distinct values in each record say delhi, bihar, agra, jaipur etc, what will be the query for that????? its not possible to update thousands of records one by one. is there any alternative ...? Plz help ... its urgent Thanx in advance

1 Answers  

How do I delete a sql server database?

0 Answers  

sql server architecture ?

0 Answers