Andrew Walbran wrote "You will need to use either a subquery or a join to do this."

I was actually reading the manual, specifically on the JOIN command, when I received your reply. I have created a couple test tables in my database, to try and see if I can change an item in one table, based on the setting in the other.

Does anybody know if there is a "limit" to the length of the string you can type into phpMySQLAdmin?

The following line works fine as I expect it to, with no errors, and gives the appropriate output:

SELECT * FROM jefftest_shuffle INNER JOIN jefftest_answer ON jefftest_shuffle.id = jefftest_answer.id WHERE jefftest_answer.answer = 'all'

But this line below gives the following error (note that this line is exactly the same from the word FROM to the end):

UPDATE jefftest_shuffle SET shuffle = 0 FROM jefftest_shuffle INNER JOIN jefftest_answer ON jefftest_shuffle.id = jefftest_answer.id WHERE jefftest_answer.answer = 'all'

Error

SQL query:

UPDATE jefftest_shuffle SET shuffle =0 FROM jefftest_shuffle INNER JOIN jefftest_answer ON jefftest_shuffle.id = jefftest_answer.id WHERE jefftest_answer.answer = 'all'

MySQL said:

There is probably a more efficient way to do this using joins.

You can't use FROM with the UPDATE query.

Okay, I found it...

You have to do the JOIN after the UPDATE, but before the SET. I also figured out that instead of the word JOIN, you can use a comma. So it looks like this:

UPDATE mdl_question_multichoice,mdl_question_answers SET mdl_question_multichoice.shuffleanswers =0 WHERE mdl_question_answers.answer = 'All of the above.' AND mdl_question_answers.question = mdl_question_multichoice.question

The problem we had was that about 450 mutiple choice questions were entered, where one of the answers was ALL OF THE ABOVE, but the Shuffle Answers was set to Yes, so the All Of The Above answer could show up anywhere in the list of answers. We needed to set Shuffle Answers to No on these questions.

Explanation:

UPDATE mdl_question_multichoice,mdl_question_answers  First of all, we pick the two tables we will be using and put a comma between them. This tells MySQL that you are going to do something with both tables.

SET mdl_question_multichoice.shuffleanswers =0  Second, we tell it what we want to change. In this case I want to set the shuffleanswers column equal to zero, in the mdl_question_multichoice table.

WHERE mdl_question_answers.answer = 'All of the above.'  Thirdly we set  a condition. This looks for the exact wording ALL OF THE ABOVE. in the answer column of the mdl_question_answers table.

AND mdl_question_answers.question = mdl_question_multichoice.question  Lastly there is another condition that needs to be met. This checks the question column in one table against the question column in the other table to make sure they are looking at the same question in both tables.

It worked perfectly on our database. Thanks everybody.