• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

group update mysql query

 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following mysql tables(tests,questions) with the corresponding columns types. The field correct_answer of questions table can hold a value equal to 'yes' or 'no'. When it is 'yes', it is counted as correct. When it is 'no',it is counted as incorrect. The fields correct and incorrect in table tests holds the sum of those counts. I had wanted a single sql command that does the update of the tests table based on the values in questions table. A record is initially inserted in tests table with the counts put to 0 while the table questions is filled up progressively.

tests(test_id integer primary key, correct integer, incorrect integer)

questions(test_id integer foreign key, question varchar(35), correct_answer varchar(3))

Test data

tests
10,0,0
11,0,0

questions
10,'textbook','yes'
10,'fire','no'
10,'card','yes'
11,'lamp','yes'

After I run the sql command, the tests table must read:

10,2,1
11,1,0

I tried "update tests set correct=select count(test_id) from questions where correct_answer='oui',incorrect=select count(test_id) from questions where correct_answer='non'" but does it not work. I just do not know where and how to do the grouping. Your help is welcome.
Kind regards



 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not just do this as two updates?
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How will that be done?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:How will that be done?

Actually now that I try it, I realize it can be done in one statement.

Try this:


It uses a subquery to tie the cound to the test_id for the current row in the tests table.

Also, note that your test data uses Yes/No and your SQL uses oui/non. Remember to adjust one
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne, i am sorry for the break in our last communication. It was getting to 2am at my end. I tried your command and it works perfectly. Thanks a lot.



 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul,
No worries. That's the great thing about async communication. Everyone can reply in a timezone convenient to him/herself!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic