How to update SQL with single query and not with php loop?

Question

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 tried this:

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

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)

http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — Cyclonecode, May 22 '13 at 10:41
It would help me more if you could tell me why is not soultion dont work. — user2301881, May 22 '13 at 11:09
When you say it's not working do you mean that the query won't run, or that the incorrect rows are updated? — Cyclonecode, May 22 '13 at 11:17
You're using a field called `id` that doesn't exist in the table `tbl_questions`, also what is `num` in your query? — Cyclonecode, May 22 '13 at 11:42
`num` would be `answercount` and... id. Is that valid for that? `tbl_questions.qid` ? — user2301881, May 22 '13 at 12:16
Why are you trying to maintain a parallel count when you can query the database for the count whenever you need it? — Neil, May 22 '13 at 15:27
Becouse the database have got more than 1 million rekords and it will be bigger and bigger... Small optimization... — user2301881, May 22 '13 at 15:55

user2301881 · Accepted Answer · 2013-05-22T13:07:17.083

0

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

edited May 22 '13 at 13:07

answered May 22 '13 at 12:29

user2301881

320
5
16

How to update SQL with single query and not with php loop?

1 Answers1