Monday 26 August 2013

DELETE duplicate values in a TABLE

-----------------------DELETE duplicate values in a TABLE------------------------------------------

CREATE TABLE dbo.TestTable(
Code Varchar (1) NULL,
n smallint NULL
) ON [PRIMARY]
GO
--select * from TestTable
INSERT INTO dbo.TestTable
VALUES
  ('A',1),('A',2),('A',2),('B',1),('B',2),('B',3),('C',1),('C',1),('C',2)


 ;WITH x AS
 (
 select code,n,ROW_NUMBER() over(order by Code,n)AS DPC from TestTable
 )
 DELETE
FROM x
WHERE DPC > 1

EMP, DEPT Sample script

/****** Object:  Table [dbo].[DEPT]    Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...