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

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)

It would help me more if you could tell me why is not soultion dont work. — user2301881
– user2301881, Commented May 22, 2013 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
– Cyclonecode, Commented May 22, 2013 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
– Cyclonecode, Commented May 22, 2013 at 11:42

user2301881 · Accepted Answer · 2013-05-22 13:07:17Z

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, 2013 at 13:07

answered May 22, 2013 at 12:29

user2301881

3505 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related