asked
Delete specific duplicate records
Can some one please help write a sql to delete the row number (1 and 4) or (4 and 6) from student table by keeping the other records, the idea is to delete where the marks become 0 (+5 -5 =0) Stid Marks s1 5 s2 5 s3 5 s1 -5 s1 6 s1 5
What should the final result be? Do you just want the total sum of marks per student? What should happen if a single student has 3 records that sum to 0 (like +5, -3, -2)? Also, how far have you gotten so far toward solving this? Can you post whatever you have written?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
You should always have a primary key in your table but here's the query that you can run to achieve your goal: DELETE t FROM ( SELECT stid , marks , ROW_NUMBER() OVER ( PARTITION BY Stid, Marks ORDER BY stid ) AS RowNumber FROM TableName ) t WHERE rowNumber > 1 OR ( stid = 's1' AND marks = -5 )
I understand there should be a primary key, hard coding "stid" or "marks" would not solve the issue, I have millions of records and just trying to eliminate the records where the marks would negate per student (in this case -5 + 5) there could be instance where per student it will be (-3 -2 +5) in this case 3 records should delete. You got the query can we delete without hard coding in the filter.
Since you said you want to delete 3 records if the sum of the 3 equals 0, why not just reduce it to 1 record per student with the sum of their scores? SET XACT_ABORT ON; BEGIN TRAN; SELECT stid, marks = SUM(marks) INTO #students FROM students GROUP BY stid ; TRUNCATE students; INSERT students (stid, marks) SELECT stid, marks FROM #students ; COMMIT TRAN;
I have several other columns in the flat table along with stid,marks like course, faculty around 50 columns, may be I am thinking row-by-row processing.
As a rule, try to avoid row-by-by processing whenever possible. The database engine is much more efficient operating on sets of rows rather than repeating the same operation(s) for each row individually.
22 People are following this question.
