DELETING mdl_quiz_attempts TABLE ROWS using SQL

DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Timothy Takemoto -
Number of replies: 7

Related to MDL-4464, a lot of my students have scores of zero on many tests.

Is it okay to use

DELETE FROM mdl_quiz_attempts
WHERE sumgrades = '0'

To delete all those  mdl_quiz_attempts table rows where the score is zero?

I would make sure that there are no attempts in progress and put the site into maintenance mode before I used the SQL.

Timothy

Average of ratings: -
In reply to Timothy Takemoto

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
That's not a gread idea, because there is data in other tables that is related to that one, and the other data will never be cleaned up.

You should really delete the corresponding rows from mdl_question_states, mdl_question_sessions and mdl_question_attempts too. (That list of tables is for Moodle 1.6+.)

This diagram http://docs.moodle.org/en/Quiz_database_structure may help a bit.
Average of ratings: -
In reply to Tim Hunt

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Timothy Takemoto -
Dear Tim

Phew, thanks for telling me! I was thinking of using that SQL, with I now realise, unfortunate consequences.

I will have a think. From a brief perusal of the structure, it looks like deleting the rows, may be a bit beyond my SQL expertise.

If anyone can suggest SQL for deleting all zero grade attempts, the please let me known.

Cheers,

Timothy
Average of ratings: -
In reply to Timothy Takemoto

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Judy Hsu -
I would be interested to know this too, thanks! smile
Average of ratings: -
In reply to Timothy Takemoto

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I really think you best bet is to do it with PHP

$attempts = get_records('quiz_attempts', 'sumgrades', 0);
foreach ($attempts as $attempt) {
quiz_delete_attempt(...);
}
Average of ratings: -
In reply to Tim Hunt

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Chris Potter -
Sorry for being a bit late on the posting, but I wondered if it were possible that the reason I've seen a "wrapped state missing" error could be because of deleting only the attempts from the quiz_attempts table and not using the PHP function call...?
Average of ratings: -
In reply to Tim Hunt

Re: DELETING mdl_quiz_attempts TABLE ROWS using SQL

by Timothy Takemoto -
Thank you Tim
A year later. This looks nice....

$attempts = get_records('quiz_attempts', 'sumgrades', 0);
foreach ($attempts as $attempt) {
quiz_delete_attempt(...);
}

But I do not know what to put in the ... area.
Average of ratings: -