How to update TestMan Test_Run and Test_Run_Case tables with data from Test_Run_Case_Step table?

"Duration", "Total", and "Failed" in Test_Run and Test_Run_Case tables can be calculated from Test_Run_Case_Step table as shown in the UpdateJMeterData.sql SQL script below:

-- UpdateJMeterData.sql

use TestMan; 

-- Updating Test_Run_Case table
update Test_Run_Case c join (
   select Test_Run_Case_ID as ID, sum(Duration) as Duration
   from Test_Run_Case_Step
   group by Test_Run_Case_ID
   ) s
   on c.ID = s.ID
set c.Duration = s.Duration
where c.Duration is null;

update Test_Run_Case c 
set c.Duration = 0
where c.Duration is null;

update Test_Run_Case c join (
   select Test_Run_Case_ID as ID, count(*) as Count
   from Test_Run_Case_Step
      where Success = 0
   group by Test_Run_Case_ID
   ) s
   on c.ID = s.ID
set c.Failed = s.Count
where c.Failed is null;

update Test_Run_Case c 
set c.Failed = 0
where c.Failed is null;

update Test_Run_Case c 
set c.Success = if(c.Failed=0, 1, 0)
where c.Success is null;

-- Updating Test_Run table
update Test_Run r join (
   select Test_Run_ID as ID, Count(*) as Count
   from Test_Run_Case
   group by Test_Run_ID
   ) c
   on r.ID = c.ID
set r.Total = c.Count
where r.Total is null;

update Test_Run r 
set r.Total = 0
where r.Total is null;

update Test_Run r join (
   select Test_Run_ID as ID, sum(Duration) as Duration
   from Test_Run_Case
   group by Test_Run_ID
   ) c
   on r.ID = c.ID
set r.Duration = c.Duration
where r.Duration is null;

update Test_Run r 
set r.Duration = 0
where r.Duration is null;

update Test_Run r join (
   select Test_Run_ID as ID, count(*) as Count
   from Test_Run_Case
      where Success = 0
   group by Test_Run_ID
   ) c
   on r.ID = c.ID
set r.Failed = c.Count
where r.Failed is null;

update Test_Run r 
set r.Failed = 0
where r.Failed is null;

update Test_Run r 
set r.Success = if(r.Failed=0, 1, 0)
where r.Success is null;

⇒ Archive JMeter Test Result Data

⇐ Convert JMeter Test Result to TestMan

⇑ Implementing TestMan in MySQL

⇑⇑ Test Management Tutorials