CODEWITHSUNDEEP
mysqlsql
D_R
D_R

Reputation: 4972

How to update select query with group by?

How can I update the row total_talktime on mobile_users table with the SUM of this following query with the correctuser_id? using group by?

This is the Select query:

SELECT SUM( TIME_TO_SEC( TIMEDIFF( `calls`.`finished_call` , `calls`.`start_call` ) ) ) AS `t` , `mobile_users`.`id`
FROM `mobile_users` 
LEFT JOIN `calls` ON `calls`.`user_id` = `mobile_users`.`id`
GROUP BY `mobile_users`.`id`

How could I make it work with Update Select, incl group by?

Upvotes: 0

Views: 80

Answers (3)

zxc
zxc

Reputation: 1526

I assume that your query is working try this

update table   
set total_talktime = (select t from  
(SELECT SUM (TIME_TO_SEC (TIMEDIFF ( `calls`.`finished_call` , `calls`.`start_call`  ) ) ) AS `t`, `mobile_users`.`id`
FROM `mobile_users` 
LEFT JOIN `calls` ON `calls`.`user_id` = `musers`.`id`
GROUP BY `mobile_users`.`id` ) as subquery)

Upvotes: 1

Sunil Tandon
Sunil Tandon

Reputation: 151

I haven't tested the query but hope this helps:

update table
set total_talktime  = derived.t
from table, 
(
SELECT SUM( TIME_TO_SEC( TIMEDIFF( `calls`.`finished_call` , `calls`.`start_call` ) ) ) AS `t` , `mobile_users`.`id`
FROM `mobile_users` 
LEFT JOIN `calls` ON `calls`.`user_id` = `mobile_users`.`id`
GROUP BY `mobile_users`.`id` 
) derived
where table.userId = derived.id

Upvotes: 0

m-oliv
m-oliv

Reputation: 419

Try:

UPDATE mobile_users SET total_talktime = (SELECT SUM( TIME_TO_SEC( TIMEDIFF( calls.finished_call , calls.start_call ) ) ) AS t , mobile_users.id FROM mobile_users LEFT JOIN calls ON calls.user_id = musers.id GROUP BY mobile_users.id )

Upvotes: 0

Related Questions