I want to do the following in one SQL statement (for efficiency reasons) but am too lazy to learn enough SQL to do it. Can someone help me please?
if ($states = get_records_list('question_states', 'event', '3,6')) {
foreach ($states as $state) {
set_field('question_sessions', 'isgraded', 1, 'attemptid', $state->attempt, 'questionid', $state->question);
}
}
I did not have an isgraded column but this might get you started
UPDATE mdl_question_sessions as se, mdl_question_states as st
SET se.isgraded=1
WHERE (st.event=3 or st.event=6) and st.attempt=se.attemptid and st.question=se.questionid
Thank you Anthony, that is simple enough. Basically I didn't know that one can join tables in UPDATE so easily.
You don't have an 'isgraded' field because that is just something that I am considering introducing tonight into the question_sessions table in order to cleanly distinguish between ungraded attempts and those that attracted a mark of 0. However I am also considering alternatives.
You don't have an 'isgraded' field because that is just something that I am considering introducing tonight into the question_sessions table in order to cleanly distinguish between ungraded attempts and those that attracted a mark of 0. However I am also considering alternatives.
Gustav,
a word of caution - table joins in updates was only available from MySql 4.0.4. I actually can't find a statement of the minimum MySql version pre Moodle version 1.6 (>4.1.16), so I'm not sure if this actually matters.
a word of caution - table joins in updates was only available from MySql 4.0.4. I actually can't find a statement of the minimum MySql version pre Moodle version 1.6 (>4.1.16), so I'm not sure if this actually matters.
Howard, you can find the requirements on moodle/admin/environment.php
Moodle Version
1.5.3+
Moodle Version
1.6 upwards
| Name | Information | Report | Status |
|---|---|---|---|
| database | mysql | version 3.23 is required and you are running 5.0.18 | OK |
| php | version 4.1.0 is required and you are running 5.1.2 | OK | |
| php_extension | mbstring | is recommended to be installed/enabled | OK |
| Name | Information | Report | Status |
|---|---|---|---|
| database | mysql | version 4.1.16 is required and you are running 5.0.18 | OK |
| php | version 4.3.0 is required and you are running 5.1.2 | OK | |
| php_extension | iconv | is recommended to be installed/enabled | OK |
| php_extension | mbstring | is recommended to be installed/enabled | OK |
Shouldn't that be in the documentation somewhere? Where's Helen 
Anyway that settles it, no joins in updates before 1.6.
Anyway that settles it, no joins in updates before 1.6.
Not a problem, I created one just to test the query as I figured you were up to something. Thanks for all that you do to continue to develop the quiz module.
In the end I decided against introducing that extra field and instead I am using the 'event' field in the question_states table to determine whether a state has been graded. That is now in CVS.
But this little lesson on SQL was interesting nevertheless and what I have learned will be useful in the future. So thanks again for the help.
But this little lesson on SQL was interesting nevertheless and what I have learned will be useful in the future. So thanks again for the help.
Uhm,
not tested at all but, are you sure that the sentence will work under PostgreSQL properly? I hadn't seen such type of SQL updates (more than one table in the first line) before. It doesn't sound as "standard" SQL.
Something like this should be more usual:
or this:
(not tested at all, aliases could work differently in PostgreSQL!)
(I use to prefer the 1st one)
Ciao
not tested at all but, are you sure that the sentence will work under PostgreSQL properly? I hadn't seen such type of SQL updates (more than one table in the first line) before. It doesn't sound as "standard" SQL.
Something like this should be more usual:
UPDATE mdl_question_sessions se
SET se.isgraded = 1
WHERE EXISTS (
SELECT 'x'
FROM mdl_question_states st
WHERE st.attempt = se.attemptid AND
st.question = se.questionid AND
(st.event=3 OR st.event=6)
)
or this:
UPDATE mdl_question_sessions se
SET se.isgraded = 1
WHERE (se.attemptid, se.questionid) IN (
SELECT st.attempt, st.question
FROM mdl_question_states st
WHERE st.event=3 or st.event=6
)
(not tested at all, aliases could work differently in PostgreSQL!)
(I use to prefer the 1st one)
Ciao
RTM. The join syntax is find in Postgres: http://www.postgresql.org/docs/8.1/interactive/sql-update.html
(and the subquery syntax probably does not work in MySQL.)
(and the subquery syntax probably does not work in MySQL.)
Hey,
What is the meaning of RTM?
Subqueries are available since MySQL 4.1.12 so it shouldn't be a problem to use them under Moodle 1.6. Also, I haven't found anything in the PostgreSQL doc allowing the initial sentence syntax to work.
Were you pointing to that page for any other reason, RTM perhaps? Just if I knew its meaning....
Ciao
What is the meaning of RTM?
Subqueries are available since MySQL 4.1.12 so it shouldn't be a problem to use them under Moodle 1.6. Also, I haven't found anything in the PostgreSQL doc allowing the initial sentence syntax to work.
Were you pointing to that page for any other reason, RTM perhaps? Just if I knew its meaning....
Ciao
What is the meaning of RTM?
"Read the manual." Some people insert an F as the third letter to make it ruder.
Were you pointing to that page for any other reason?
Yes, that page gives the documentation for the Postgres update command. If you look at the paragrpahs about the fromlist and condition parts of the syntax, that makes it clear that the join syntax works.
"Read the manual." Some people insert an F as the third letter to make it ruder.
Were you pointing to that page for any other reason?
Yes, that page gives the documentation for the Postgres update command. If you look at the paragrpahs about the fromlist and condition parts of the syntax, that makes it clear that the join syntax works.
Oh, not need to write the F at all! I consider it's enough rude without it.
Also, perhaps we are talking about different sentences, dear Tim, because I cannot find any fromlist in the initial sentence suggested in this discussion.
Anyway, both the fromlist solution from PostgreSQL and the suggestions posted at the beginning (MySQL) seem to be proprietary solutions not working in other DBs. That was the reason I wrote my suggestions. The sub-query alternatives sound like the best solution, at least in my non-expert, non-rtm, humble, non-sql-guy opinion.
And finally, is it true that "that makes it clear that the join syntax works", have you tried it? Perhaps could we TIF before OOM? 
Ciao
P.S: I haven't PostgreSQL so I cannot TIF, but I'm pretty sure that you can. The curiosity is killing me, does it work?
Also, perhaps we are talking about different sentences, dear Tim, because I cannot find any fromlist in the initial sentence suggested in this discussion.
Anyway, both the fromlist solution from PostgreSQL and the suggestions posted at the beginning (MySQL) seem to be proprietary solutions not working in other DBs. That was the reason I wrote my suggestions. The sub-query alternatives sound like the best solution, at least in my non-expert, non-rtm, humble, non-sql-guy opinion.
And finally, is it true that "that makes it clear that the join syntax works", have you tried it? Perhaps could we TIF before OOM?
Ciao
P.S: I haven't PostgreSQL so I cannot TIF, but I'm pretty sure that you can. The curiosity is killing me, does it work?
I consider it's enough rude without it.
I know, and I did not mean to be rude, particularly to you. I am just having a bad week month, but that is no excuse.
I have just tried, and both syntaxes work. However, in the moodle context, your subquery syntax is better, and in the bit of code I have already written that needed to do this, I just found I was using the subquery syntax:
I suppose the important distinction would be if one gives much better performance, but I doubt it.
I know, and I did not mean to be rude, particularly to you. I am just having a bad week month, but that is no excuse.
I have just tried, and both syntaxes work. However, in the moodle context, your subquery syntax is better, and in the bit of code I have already written that needed to do this, I just found I was using the subquery syntax:
delete_records_select('workflow_state_transitions',
"instanceid IN (SELECT id FROM {$CFG->prefix}workflow_task_instances WHERE courseid = '$courseid')");
That fits reasonably well with the dataloib api. However, it is quite hard to turn
DELETE FROM mdl_workflow_task_state_transitionsinto a datalib function call.
USING mdl_workflow_task_instances AS ti
WHERE mdl_workflow_task_state_transitions.instanceid = ti.id AND ti.courseid = '1';
I suppose the important distinction would be if one gives much better performance, but I doubt it.
Hey Tim,
no problem at all, sincerely! It was an entertaining discussion, just it. And that type of discussions are really great to take some distance (a break*) from the tedious-daily-stress and to spend some minutes talking, discussing and, why not, being a bit rude (without losing the humour, of course).
Cheers and ciao
* In Spanish to do a break we use the expression "to do a parenthesis". Just wondering if it would be the same in English too...
no problem at all, sincerely! It was an entertaining discussion, just it. And that type of discussions are really great to take some distance (a break*) from the tedious-daily-stress and to spend some minutes talking, discussing and, why not, being a bit rude (without losing the humour, of course).
Cheers and ciao
* In Spanish to do a break we use the expression "to do a parenthesis". Just wondering if it would be the same in English too...
Normally in postgresql "in" syntax is slower than "join", in particular if the returning dataset gets a bit big. There are other factors such as proper use of indexes, vacuum analyze. I think the performance difference is now smaller on 7.4.x of postgresql.
Deleting would normally not happen everytime, so I guess the performance aspect is not much of an issue.
Deleting would normally not happen everytime, so I guess the performance aspect is not much of an issue.