Delete duplicate records from the table?(Table must have
unique id)
Answers were Sorted based on User's Feedback
Answer / dinesh kumar
delete from emp where id Not in(select max(id) id from emp
group by name having count(id)>1)
Is This Answer Correct ? | 22 Yes | 9 No |
Answer / lince
DELETE
FROM MyTable
WHERE ID IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1 HAVING COUNT(ID)>1
)
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / kk
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
Is This Answer Correct ? | 7 Yes | 7 No |
Answer / naren
delete id from table where id in(select id from table having count(id)>1)
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / krishna
select *
from mytable b
join
(
select Duplicatecolumn,MAX(id) id
from mytable
group by Duplicatecolumn
) b1 on b.Duplicatecolumn = b1.Duplicatecolumn
where b.id <> b1.id
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / brijesh darmwal, sandhya
DELETE FROM [MyDb].[dbo].[sandhya]
WHERE id
IN
(SELECT id
FROM
(SELECT MAX(id) as id,name,addr
FROM [MyDb].[dbo].[sandhya] GROUP BY name,addr having
count(id)>=2)
Tmp)
Is This Answer Correct ? | 7 Yes | 16 No |
Syntax to find the used space and free space of the Data file in a database(SQL Server). Following queries didn't give the exact Used space and Free Space Information sp_spaceused;DBCC showfilestats;exec MyDbName.dbo.sp_spaceused;SP_HELPFILE Can any one tell me the query for how to find the exact used data file space and free space in a Data File?
What happens if you are trying to access a schema not owned by you?
What extended events?
How can we solve concurrency problems?
What is oltp (online transaction processing)?
What are ddl (data definition language) statements for tables in ms sql server?
Does partitioning help performance?
When you should use a low fill factor?
How to generate random numbers with the rand() function in ms sql server?
What does it mean to be in union?
What is bulkcopy in sql?
How to perform backup for certificates in sql server? : sql server security