Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

These are my tables:

tbl_answers => 
  aid     
  qid     
  answer     
  uid     
  dateposted     
  emailnotify
  namedisplay
  status
  isbestanswer 


tbl_questions =>
  qid
  question
  detail
  mcid
  cid
  uid     
  answercount
  dateposted
  status
  showname
  emailnotify
  question_type_id  

I woud like to update those question that has got more answers than it's counted in:
tbl_questions => answercount

How can I update the rows which have got more questions than it's counted? (Without php loop)

This is how I'm trying now:

UPDATE tbl_questions
  JOIN (
    SELECT tbl_questions.qid, COUNT(*) AS n 
    FROM tbl_questions JOIN tbl_answers ON tbl_answers.qid = tbl_questions.qid 
    WHERE answercount = "0" 
    GROUP BY id
  ) AS T USING (tbl_answers.qid)
SET answercount = n 
WHERE n > 0

IT says me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.qid) SET answercount = n WHERE n > 0' at line 7

share|improve this question
    
Try USING (qid) instead of USING (tbl_answers.qid) –  ypercube May 22 '13 at 12:41
    
That's fully working, thank you very much! :-D –  user2301881 May 22 '13 at 12:44
    
And the GROUP BY id is probably a typo? I don't see an id column anywhere. –  ypercube May 22 '13 at 12:47
    
That is GROUP BY qid, yes, I forgot q from there.. :) –  user2301881 May 22 '13 at 12:56

1 Answer 1

up vote 1 down vote accepted

Wouldn't this be sufficient for your update, since the subquery providing the update value evaluates to 0 if there aren't any answers for your question.

UPDATE tbl_questions q
SET q.answercount = (SELECT COUNT(*) FROM tbl_answers a WHERE a.qid = q.id)
WHERE q.answercount = 0
share|improve this answer
    
That look like better. –  user2301881 May 22 '13 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.