Updating quiz questions, en mass

A few weeks back, upon initial course set up, we discovered a problem with the graphics displaying in a course quiz.  This is a calculus course from a vendor.  When i looked at the questions closely, I discovered that by changing the path to the image to the courseid, it found the graphic correctly.  This course has about 40 quizzes, so I wanted to automate the updating.  I remembered the replace utility here in moodle

http://siteURL/admin/tool/replace/

I went ahead and replaced all the references in the of the courseid in the path with the new courseid.

This was a great solution for that course, but it also updated all the records for another course that it should not have.  I am not sure how this happened, we had backed up and restored the course from the same master and it must have shared the same courseid.  The bottom line is another course had been updated too.

The error was reported to me from a customer who was taking this *copy of the course with broken quiz image links. 

I set off to the DB to see if I could isolate the quiz questions for the broken course with the intention of updating the records in the DB, rather than through the moodle quiz interface (just a lot faster with lots of questions).

My initial foray resulted in me opening the mdl_question table and isolating the course questions with a simple SQL statement using a where clause like this:

use db name
edit mdl_question
where questiontext like '%part of the path with id in it%'

I was able then to update the records one a time in the db.

I checked the course and same that my updates where correcting the problems.  About a week later, my customer contacted me again and said some of the quizzes where still broken.  Urrrr, I thought I had corrected them.

Upon further review, I noticed that the multiple choice questions had a separate table that also needed updating.  In hindsight, this is obvious.

I checked first my thinking by joining the two tables to cross check the result using this query

select * from mdl_question_answers qa
join mdl_question q on q.id = qa.question

where qa.answer like '%quiz path with courseid%'

After this I editing the mdl_question_answers table much like the mdl_question table and verified the quiz images rendering correctly.

Moral of the story?  

Some math courses use A LOT of images, since the symbols are hard to duplicate using text. 

If a course is heavy in quizzes and you want to modify the questions directly in the DB, know the structure and how they relate.  Look in both the mdl_question table and other related mdl_tables.