-- Hapus data kartu yang dobel

-- step 1: cek yg dobel
SELECT
   No_Proposal ,
   No_Peserta ,
   jml
FROM (    
    SELECT
          No_Proposal ,
          No_Peserta,
          COUNT(*) AS jml
    FROM dbo.Kartu_Peserta 
    WHERE No_Proposal='2014.0382'
    GROUP BY No_Proposal ,
          No_Peserta
) AS x
WHERE x.jml>1



-- step 2: hapus yang dobel

DECLARE
  @no_peserta VARCHAR(13)

DECLARE kartu_dobel CURSOR FOR  
SELECT
  No_Peserta
FROM (    
    SELECT
          No_Proposal ,
          No_Peserta,
          COUNT(*) AS jml
    FROM dbo.Kartu_Peserta 
    WHERE No_Proposal='2014.0382'
    GROUP BY No_Proposal ,
          No_Peserta
) AS x
WHERE x.jml>1

OPEN kartu_dobel  
FETCH NEXT FROM kartu_dobel INTO @no_peserta 

WHILE @@FETCH_STATUS = 0  
BEGIN 
    DECLARE @id_kartu VARCHAR(11) 
    SELECT TOP 1
      @id_kartu=Id_Kartu
    FROM 
      dbo.Kartu_Peserta 
    WHERE 
      No_Proposal='2014.0382'
      AND No_Peserta=@no_peserta
      
    --SELECT @id_kartu
    
    DELETE FROM dbo.Kartu_Peserta WHERE No_Proposal='2014.0382' AND No_Peserta=@no_peserta AND Id_Kartu=@id_kartu

    FETCH NEXT FROM kartu_dobel INTO @no_peserta  
END  

CLOSE kartu_dobel  
DEALLOCATE kartu_dobel 