mysql - SQL update table attribute after table join -

new sql.

i have 2 tables. client:

client_id  client_name status 1               jz       null 2               kd       null 3               tf       null 

and

transactions:

transaction_id    amount client_id   1                     5     1         2                     5     2         3                     5     3         

i can join follows:

select client.status, client.client_id, client.client_name, sum(transactions.amount) balance  client  join transactions on transactions.client_id=client.client_id  group client.client_id  order client_id  

and result:

client_id   client_name   balance  status 1              jz           5       null 2              kd           5       null 3              tf           5       null 

however, update value in 'status' 'on' if client balance >=0, , 'off' if <0. possible updates 'client' table?

your query has syntax errors, answer question case expression:

select c.client_id, c.client_name, sum(t.amount) balance,        (case when sum(t.amount) < 0 'off' else 'on' end) status client c join      transactions t      on t.client_id = c.client_id  group c.client_id  order c.client_id, c.client_name; 

if want update value in table need update. 1 method should work on databases is:

update client      set status = (select (case when sum(t.amount) < 0 'off' else 'on' end)                    transactions t                    t.client_id = client.client_id                   ); 

however, wouldn't recommend doing this. next transaction invalidate statuses. now, better write query information when need it.