Mysql – SQL update query with join and subquery

MySQL

I am trying to update the values of a column to the results of a sub query. I get the error that the sub query is returning more that one row, which it should. It should be returning a column of values.

Update companies
set c_c_score = ( select t.summ 
              from ( select sum(case when b.hot_tip = 1 
                                     then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) 
                                     else 0 
                                     end) as summ
                     from companies a
                     left join table2 b
                         on a.id = b.company_id
                     left join table3 c
                         on b.table3_id = c.id
                     group by a.id ) AS t);

The sub query runs fine by itself.

    select a.id,  sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end)
        from companies a
        left join table2 b
        on a.id = b.company_id
        left join table3 c
        on b.table3_id = c.id
        group by a.id;

Best Answer

This seems to work:

Update companies c
inner join (select a.id as company_id, sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end) as cc_score
from companies a
left join table2 b
on a.id = b.company_id
left join table3 c
on b.table3_id = c.id
group by a.id) x ON x.company_id = c.id
set c.c_c_score = x.cc_score;

Related Solutions

Mysql – Optimizing a query so MySQL will use the indexes for the joins

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Best Answer

Related Solutions

Mysql – Optimizing a query so MySQL will use the indexes for the joins

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

Related Question