Update a MySQL table to double aggregate of a construct, which depends on the table itself

Question

I need to update a table, but to get the new value it seems that I need to create a temporary table. The reason is that I need to calculate sum of the max. Can I do it?

The pseudocode looks like this:

 UPDATE users u SET usersContribution = [CREATE TEMPORARY TABLE IF NOT EXISTS tmpTbl3 AS
          (SELECT ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
           FROM zachet zz
           JOIN discussionansw d ON d.zachetid=zz.zachetId and d.usersid=zz.usersId and
                                    zz.zachetTimestamp > d.answerDate
           WHERE zz.whoTalk=u.userid and
                 NOT EXISTS (SELECT * FROM discussionansw
                            WHERE zachetid=zz.zachetId and usersid=u.userid')
           GROUP BY zz.zachetId)]
           SELECT SUM(answerDateDiff) FROM tmpTbl3;"

I used a brackets to show the part, which have to be done, but ignored by UPDATE query...

I have both max and sum and I do not see a way to avoid tmp table. But if you can I we'll be glad to have such a solution.

---

I put here THE ANSWER, which I get with help of @flaschenpost and this post: SQL Update to the SUM of its joined values

 CREATE TEMPORARY TABLE IF NOT EXISTS t0tmpTbl3 AS
          (SELECT zz.whoTalk, ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
           FROM zachet zz
           JOIN discussionansw d ON d.zachetid=zz.zachetId and d.usersid=zz.usersId and
                                    zz.zachetTimestamp > d.answerDate
           WHERE 
                 NOT EXISTS (SELECT * FROM discussionansw WHERE zachetid=zz.zachetId and usersid=zz.whoTalk)
           GROUP BY zz.zachetId);

 UPDATE users u
           JOIN (SELECT whoTalk, SUM(answerDateDiff) sumAnswerDateDiff
                 FROM t0tmpTbl3 GROUP BY whoTalk) t
           ON u.usersId=t.whoTalk
           SET u.usersContribution=sumAnswerDateDiff;

Answer 1

Could you just break it into two Queries?

drop temporary table if exists tmp_maxsumofsomething;

create temporary table tmp_maxsumofsomething 
select max(), sum(), ...
from zachet z inner join discussionansw a on ...
group by...
;

update u inner join tmp_maxsumofsomething t on ... set u.... = t...

Temporary Tables are just visible in the connection where they have been created, so Thread Safety is given.

EDIT: As long as your Queries make any sense, you could try:

 DROP TEMPORARY TABLE IF EXISTS tmpTbl3;
 CREATE TEMPORARY TABLE tmpTbl3
      SELECT zz.whoTalk as userId, ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
       FROM zachet zz, discussionansw d
      WHERE  d.zachetid=zz.zachetId 
        and d.usersid=zz.usersId and zz.zachetTimestamp > d.answerDate
            # What do you mean ? by: 
            # and (SELECT count(*) FROM discussionansw
            #      WHERE zachetid=zz.zachetId and usersid=u.userid) = 0
            # Think about a reasonable WHERE NOT EXISTS clause!
     GROUP BY zz.whoTalk

Then you have your Temp-Table to join to:

 update users u 
 inner join tmpTbl3 tm on u.userId = tm.userId 
 set u.usersContribution = tm.answerDateDiff

If you are brave enough to write an application needing those queries, you should not be scared to learn a bit more of some concepts of SQL and MySQL. You are here for the exploration of concepts, not to hire Programmers for free.