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;

that is massively overcomplicated. you can easily have `update ... select` with join clauses to get "foreign" values. — Marc B, May 02 '14 at 18:23
@MarcB, then could you kindly and easily tell me how, please?:) — klm123, May 02 '14 at 18:26
@MarcB, are you sure you are talking about my case? I don't see there any examples with max or something like this. — klm123, May 02 '14 at 18:32
the `join`ed table can be a select. e.g. `update t1 join (select ...) AS foo on t1.bar = foo.baz` — Marc B, May 02 '14 at 18:39
@MarcB, thanks, but I still do not see it the way to do it. I use select to create a virtual tables with max values, but then I need to say how to group those, who have save usersid and SUM them up on fly... Please see my attemp added to the quesition. — klm123, May 02 '14 at 18:58

flaschenpost · Accepted Answer · 2014-05-02T20:29:24.733

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.

edited May 02 '14 at 20:29

answered May 02 '14 at 19:14

flaschenpost

2,205
1
14
29

the temporary depends on u. and thereis sum(max()), not sum,max. If i can, you can do it to, but I do not get your idea. – klm123 May 02 '14 at 19:19
I updated my question using EXISTS statement. Your answer still gives no hint about what should be done with u.userid. I will be glad to learn more SQL concepts, but I do not have time to learn them all and I don't know what do I need here. I tried union and temporary table with no success. – klm123 May 03 '14 at 07:05
The first table creates the answer for every user which has zachet and discussionansw, and stores those many entries in tmpTbl3. The second table simply copies the results to table user. Could you give any hint about your condition count(*) = 0? should there be no other answers of that user? – flaschenpost May 03 '14 at 09:34
oh. Now I see your idea. I'll try it. The only thing is missed in your answer is SUM, but it can be implemented with additional tmpTable. Don't know whether it is optimal, but it should work. – klm123 May 03 '14 at 10:00
yes, there should be no answers from this user. I changed this part in the question post. – klm123 May 03 '14 at 10:01

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

1 Answers1