How to remove the duplicate records based on memberid

Transact-SQL
1

Dear all,
I tried to remove the duplicate records from one table but not helpful, in my table there are 3 field
id memberid name
1 1 ABC
1 2 BCD
1 1 ABC
1 3 GCD
1 6 PLX

I want

id memberid name
1 1 ABC
1 2 BCD
1 3 GCD
1 6 PLX

Please help[

2

try this, bit long winded but should get the job done. will need amending to handle multiple duplicated records but should get you started,

;with ctedata as (select 1 as idnum, 1 as memberid, 'ABC' as name
union all select 1, 2, 'BCD'
union all select 1, 1, 'ABC'
union all select 1, 3, 'GCD'
union all select 1, 6, 'PLX')

select *
into #members
from ctedata;

select memberid into #memberDups from #members group by memberid having count(*) > 1;

while exists(select * from #memberDups)
begin
declare @currmem int;
select @currmem = memberid from #memberDups;

	with cte_memDs as (select top 1 * from #members where memberid = @currmem)
	delete from cte_memDs;
	
	delete from #memberDups where memberid = @currmem;
end

select * from #members;

drop table #members;
drop table #memberDups;

3

DECLARE @TBL TABLE( id INT, memberid INT, name VARCHAR(10))
INSERT INTO @TBL VALUES (1, 1, 'ABC')
INSERT INTO @TBL VALUES (1, 2, 'BCD')
INSERT INTO @TBL VALUES (1, 1 ,'ABC')
INSERT INTO @TBL VALUES (1, 3, 'GCD')
INSERT INTO @TBL VALUES (1, 6, 'PLX')

--SELECT *, ROW_NUMBER()OVER(PARTITION BY ID, MEMBERID, NAME ORDER BY NAME) AS ROWNUM
-- FROM @TBL

;WITH CTE
AS(
SELECT *, ROW_NUMBER()OVER(PARTITION BY ID, MEMBERID, NAME ORDER BY NAME) AS ROWNUM
FROM @TBL
)

DELETE FROM
CTE WHERE ROWNUM>1

SELECT * FROM @TBL

2 Likes
4

Do you not have any column in the table which uniquely identifies the row? Deleting DUPs is much easier if you do :slight_smile:

5

nice solution @rocknpop, never thought to use the rownum partition.

:smile:

6

Rows are not records. Fields are not columns. One of the many, many ways that rows are not records is that you can have UNIQUE constraints., This is why all SQL forums require DDL and sample data.

Clean up the mess any way you wish, then add constraints. "Mop the floor, then fix the roof"