Bagaimana menghapus duplikat dari tabel di SQL Server

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 139444 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

RINGKASAN

Microsoft SQL Server tabel tidak pernah harus berisi duplikasi baris, atau non-unik kunci primer. Untuk singkatnya, kita akan kadang-kadang merujuk utama kunci sebagai "kunci" atau "PK" di artikel ini, tetapi ini akan selalu menunjukkan "utama kunci." PKs duplikat pelanggaran entitas integritas, dan harus batasan dalam sistem relasional. SQL Server memiliki berbagai mekanisme untuk menegakkan entitas integritas, termasuk indeks, unik kendala, PRIMARY KEY kendala, dan memicu.

Meskipun demikian, di bawah kondisi yang tidak biasa duplikat kunci primer dapat terjadi, dan jika demikian mereka harus dihilangkan. Salah satu cara mereka dapat terjadi adalah jika duplikat PKs ada di non data relasional di luar SQL Server, dan data diimpor sementara PK keunikan tidak ditegakkan. Cara lain yang mereka dapat terjadi melalui sebuah desain database error, seperti tidak menegakkan entitas integritas di setiap meja.

Sering duplikat PKs menyadari ketika Anda mencoba untuk membuat indeks unik, yang akan membatalkan jika duplikat kunci ditemukan. Pesan ini adalah:
MSG 1505, tingkat 16, negara bagian 1 Membuat indeks unik dibatalkan pada tombol duplikat.
Jika Anda menggunakan SQL Server 2000 atau SQL Server 2005, Anda mungkin menerima pesan galat berikut:
MSG 1505, tingkat 16, negara 1 membuat indeks unik dihentikan karena duplikat kunci ditemukan untuk nama objek ' %. * ls' dan indeks nama ' %. * ls'. Nilai kunci duplikat adalah % ls.
Artikel ini membahas cara untuk mencari dan menghapus duplikat kunci primer dari meja. Namun, Anda harus dekat memeriksa proses yang memungkinkan duplikat terjadi untuk mencegah terulangnya.

INFORMASI LEBIH LANJUT

Untuk contoh ini, kita akan menggunakan tabel berikut dengan nilai-nilai PK duplikat. Dalam tabel ini kunci utama adalah dua kolom (col1, col2). Kita tidak bisa membuat indeks unik atau PRIMARY KEY kendala karena dua baris memiliki PKs duplikat. Prosedur ini menggambarkan bagaimana mengidentifikasi dan menghapus duplikat.
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
				
Langkah pertama adalah untuk mengidentifikasi baris yang memiliki nilai-nilai kunci primer duplikat:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
				
Ini akan kembali satu baris untuk setiap rangkaian duplikat PK nilai dalam tabel. Kolom terakhir dalam hasil ini adalah jumlah duplikat untuk nilai PK tertentu.

Perkecil tabel iniPerbesar tabel ini
col1col2
112


Jika hanya beberapa set nilai PK duplikat, prosedur terbaik adalah untuk menghapus ini secara manual secara individual. Misalnya:
set rowcount 1
delete from t1
where col1=1 and col2=1
				
Nilai rowcount harus n-1 jumlah duplikat untuk nilai kunci tertentu. Dalam contoh ini, ada 2 duplikat begitu rowcount diatur ke 1. Col1/col2 nilai yang diambil dari di atas GROUP BY query result. Jika grup oleh permintaan kembali beberapa baris, permintaan "mengatur rowcount" akan harus dijalankan sekali untuk masing-masing baris ini. Setiap kali menjalankan, mengatur rowcount n-1 jumlah duplikat dari nilai PK tertentu.

Sebelum menghapus baris, Anda harus memverifikasi bahwa seluruh baris duplikat. Meskipun tidak mungkin, mungkin bahwa nilai-nilai PK duplikat, Namun baris secara keseluruhan tidak. Contoh ini akan meja dengan Nomor jaminan sosial sebagai primary key, dan memiliki dua orang yang berbeda (atau baris) dengan nomor yang sama, masing-masing memiliki atribut yang unik. Sedemikian kasus kerusakan apa pun yang menyebabkan tombol duplikat mungkin juga disebabkan data unik yang benar harus diletakkan di baris. Data ini harus disalin dan diawetkan untuk studi dan mungkin rekonsiliasi sebelum untuk menghapus data.

Jika ada banyak berbeda set duplikat PK nilai dalam tabel, mungkin menjadi terlalu memakan waktu untuk menghapusnya secara individual. Dalam hal ini mengikuti prosedur dapat digunakan:
  1. Pertama, berjalan di atas kelompok oleh permintaan untuk menentukan berapa banyak set duplikat PK nilai ada, dan menghitung duplikat untuk setiap set.
  2. Pilih nilai-nilai kunci duplikat ke dalam tabel memegang. Misalnya:
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
    					
  3. Pilih duplikasi baris ke dalam tabel memegang, menghilangkan duplikasi dalam proses. Misalnya:
    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  4. Pada titik ini, tabel holddups harus memiliki unik PKs, namun, ini tidak akan terjadi jika t1 duplikat PKs, namun baris unik (seperti dalam SSN contoh di atas). Verifikasi bahwa setiap key dalam holddups unik, dan bahwa Anda tidak memiliki kunci duplikat, namun unik baris. Jika demikian, Anda harus berhenti di sini dan mendamaikan yang baris Anda ingin tetap untuk nilai kunci duplikat yang diberikan. Misalnya, permintaan:
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    harus kembali hitungan 1 untuk setiap baris. Jika ya, lanjutkan ke langkah 5 di bawah ini. Jika tidak, Anda memiliki kunci duplikat, namun unik baris, dan perlu memutuskan yang baris untuk menyelamatkan. Ini biasanya akan memerlukan membuang berturut-turut, atau menciptakan nilai kunci unik baru untuk baris ini. Mengambil salah satu dari dua langkah untuk setiap PK seperti duplikat dalam tabel holddups.
  5. Menghapus duplikat dari meja asli. Misalnya:
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Meletakkan baris unik kembali di meja asli. Misalnya:
    INSERT t1 SELECT * FROM holddups
    					

Properti

ID Artikel: 139444 - Kajian Terakhir: 14 September 2011 - Revisi: 2.0
Berlaku bagi:
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Kata kunci: 
kbsqlsetup kbinfo kbusage kbmt KB139444 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.
Klik disini untuk melihat versi Inggris dari artikel ini:139444

Berikan Masukan

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com