Question: There is an Exam_Merge table into which to import records from a similarly structured table in another database. In this case, the Exam_Merge table contains the values of the primary key ID from another database, but this field is not unique. For some reason, some of the entries in it were duplicated: an entry with the same ID is contained in the table 2 times, the values of the remaining field of duplicate records also coincide.
It is necessary to remove duplicates, leaving only non-duplicate IDs.
My question - is it correct and efficient approach or something efficient way exists? sqlfiddle: http://www.sqlfiddle.com/#!18/dce35/1
CREATE TABLE exam_merge ( id INT NOT NULL, student_code NVARCHAR(10) NOT NULL, exam_code NVARCHAR(10) NOT NULL, mark INT NULL );
WITH cte AS (SELECT id, student_code, exam_code, mark, Row_number() OVER( partition BY id, student_code, exam_code, mark ORDER BY student_code) AS rn FROM exam_merge) DELETE cte WHERE rn > 1