How to delete rows inserted unintentionally with a given column value

Question

I am a beginner with MySQL. I made a stored procedure to insert 1,000 random names from a table. It has 3 fields with num, course_name and grade. num is foreign key--as this was for a test purpose, I just kept incremented the num only. So I didn't mark it as a PRIMARY KEY/AUTO_INCREMENT. I called the procedure, and it inserted 1,000 random names in the table. Unknowingly, I called the procedure again, and stopped it after some time. Then the table got 500 more entries after that previous 1000 entries. I wanted to delete the rows that created after the second procedure call.

Below is my statements in a stored procedure: (course_name and grade_details are additional tables with course names and grades.)

DELIMITER //
CREATE PROCEDURE course_grade(IN name_entries int)
BEGIN
    DECLARE i int DEFAULT 0;
    DECLARE course varchar(20);
    DECLARE crs_grade char(1);

    gradeloop : LOOP
        SELECT name INTO course FROM course_name ORDER BY rand() LIMIT 1;
        SELECT grade INTO crs_grade FROM grade_details ORDER BY rand() LIMIT 1;

        INSERT INTO tbl_grade(fk_int_roll_no,vchr_course,vchr_grade)
        VALUES (i+1,course,crs_grade);

        SET i = i + 1;

        IF (i=name_entries)
            THEN LEAVE gradeloop;
        END IF;
    END LOOP gradeloop;
    SELECT COUNT(*) FROM tbl_grade;
END //
DELIMITER ;

And my table is like :

    +----------------+-------------+------------+
    | fk_int_roll_no | vchr_course | vchr_grade |
    +----------------+-------------+------------+
    |              1 | AE          | A          |
    |              2 | MECH        | B          |
    |              3 | EC          | A          |
    |              . | ....        | .          |
    |              . | ....        | .          |
    |           1000 | IT          | E          |
    |              1 | MARINE      | F          |
    |              2 | BIOTECH     | F          |
    |              . | ....        | .          |
    |              . | ....        | .          |
    |              . | ....        | .          |
    |              . | ....        | .          |
    |            500 | RM          | A          |
    +----------------+-------------+------------+

Wanted to delete the last 1 to 500 rows made by mistake!

Possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — xQbert, Dec 24 '15 at 14:44
When i add the unique index it shows - ERROR 1062 (23000): Duplicate entry '1' for key 'fk_int_roll_no_2' — NooNa MarJa, Dec 24 '15 at 14:56
What did your alter statement look like? `ALTER IGNORE TABLE course_Grade ADD UNIQUE INDEX idx_name (fk_int_Roll_no,...);`? — xQbert, Dec 24 '15 at 14:58
@xQbert ALTER IGNORE TABLE tbl_grade ADD UNIQUE INDEX (fk_int_roll_no); — NooNa MarJa, Dec 24 '15 at 15:19
SELECT * FROM tbl_grade LIMIT 1000,1500; - give rows that are needed to be deleted. — NooNa MarJa, Dec 24 '15 at 15:23
http://stackoverflow.com/questions/6103212/how-do-i-delete-duplicate-rows-and-keep-the-first-row is a different approach if the first one didn't work. (though I can't see why it wouldn't) Several other examples on SO exist. — xQbert, Dec 24 '15 at 15:28

How to delete rows inserted unintentionally with a given column value

0 Answers0