Thursday, November 8, 2012

REMOVE DUPLICATE ROWS FROM A TABLE USING SQL

SQL FOR REMOVING DUPLICATE


create table demo(
  id int identity(1,1) not null,
  alpha nvarchar(50)
 
)
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'


select * from demo

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY alpha
                                       ORDER BY ( SELECT 0)) RN
         FROM   demo)
DELETE FROM cte
WHERE  RN > 1


select * from demo

update table from another table or same table using inner join


declare @temp table
 (
  Id int identity(1,1) not null,
  alpha nvarchar(50)

 )

 insert @temp  select null
 insert @temp  select null
 insert @temp  select null

 select * from @temp

declare @temp1 table
 (
  Id int identity(1,1) not null,
  alpha nvarchar(50)
 )


 insert @temp1  select 'A'
 insert @temp1  select 'B'
 insert @temp1  select 'C'

 select * from @temp1


 update @temp   set alpha=t1.alpha
 FROM   @temp as t    inner join  @temp1  as t1 ON t.id=t1.id



 select * from @temp