CODEWITHSUNDEEP
mysqlsql
Ranjit Kumar
Ranjit Kumar

Reputation: 781

How to simplfy this query?

Query 1: 

SET @count = 0;
    UPDATE a_daily_copy_copy
    SET a_daily_copy_copy.Cummulative_Target = @count:= target + @count 
    where a_daily_copy_copy.Site_id = 1 
          and a_daily_copy_copy.Year=4 
          and a_daily_copy_copy.Billing_cycle=1
    ORDER BY date

Query 2: Modified the a_daily_copy_copy.Billing_cycle=2 

 SET @count = 0;
    UPDATE a_daily_copy_copy
    SET a_daily_copy_copy.Cummulative_Target = @count:= target + @count
    where a_daily_copy_copy.Site_id = 1 
          and a_daily_copy_copy.Year=4 
          and a_daily_copy_copy.Billing_cycle=2
    ORDER BY date

I'm a beginner and as of now I'm running the query every time manually by editing the query 1 , and I know both queries can be consolidated into a single query.

I tried solving with Group by function but couldnt come up with Please help me.

Have screened the table: enter image description here

Upvotes: 0

Views: 64

Answers (1)

Will A
Will A

Reputation: 24988

Looks to me that you can just do:

SET @count = 0;
    UPDATE a_daily_copy_copy
    SET a_daily_copy_copy.Cummulative_Target = @count:= target + @count where a_daily_copy_copy.Site_id = 1 and a_daily_copy_copy.Year=4 and a_daily_copy_copy.Billing_cycle IN (1, 2)
    ORDER BY date

...unless I've missed a difference between the two queries other than the billing cycle.

Upvotes: 2

Related Questions