CODEWITHSUNDEEP
mysqlsql
Caco
Caco

Reputation: 1654

MySql UPDATE statement with SELECT summarizing data from table 2

I have two tables, TABLE A has columns token (primary key) and downtime (INT), TABLE B has columns token, status (ENUM with 3 states: active, unstable, inactive), duration (INT).

I want to sum duration from TABLE B, only for the states unstable and inactive. And after that, assign the result to column downtime, from TABLE A.

So, for example,

TABLE A
=======

token             downtime
--------------------------

bv87pxicnrtk8pw       null
v3525kq2kzihb9u       null

TABLE B
=======
token                 state       duration
------------------------------------------
v3525kq2kzihb9u      active              9
v3525kq2kzihb9u    unstable             20
v3525kq2kzihb9u    inactive             60
bv87pxicnrtk8pw    unstable             11
bv87pxicnrtk8pw      active            140
bv87pxicnrtk8pw    inactive             40

RESULT
======
token             downtime
--------------------------

bv87pxicnrtk8pw         51
v3525kq2kzihb9u         80

I tried

UPDATE A
SET downtime = (SELECT SUM(duration) FROM B WHERE state != 'active' GROUP BY token)
WHERE A.token = B.token;

but without success.

Newbie in SQL. Can anyone help me?

Upvotes: 1

Views: 54

Answers (3)

lp_
lp_

Reputation: 1178

Your idea to use a subquery in the update is good, you just need to reorganize it:

UPDATE A
SET downtime = (SELECT SUM(duration) FROM B WHERE state != 'active' AND A.token = B.token);

Upvotes: 0

M0rtiis
M0rtiis

Reputation: 3774

try this:

UPDATE A a
    INNER JOIN (
        SELECT SUM(duration) AS s
        FROM B
        WHERE state <> 'active'
        GROUP BY token  
    ) b ON a.token = b.token
SET a.downtime = b.s

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

In mysql you can use join and update something as

update tablea a
join(
  select 
  token,sum(duration) as duration from tableb
  where state != 'active'
  group by token
)b
on b.token = a.token
set a.downtime = b.duration

Upvotes: 1

Related Questions