SQL Help (MySQL)

Question:

I have 2 tables..

tblDetails
HFID | TID | Grade
——————–
1040 | 1 | NULL
1040 | 2 | NULL
1040 | 2 | NULL
5670 | 3 | NULL
5670 | 4 | NULL
8879 | 5 | NULL

tblMain
TID | DPI
————–
1 | 3.4
2 | 5.6
3 | 4.4
4 | 0.1
5 | 1.5

Look tblMain for DPI… add them and update tblDetails.
1+2+3 (3.4+5.6+4.4 = )

Final tblDetails
——————-
HFID | TID | Grade
1040 | 1 | 13.4
1040 | 2 | 13.4
1040 | 2 | 13.4
5670 | 3 | 4.5
5670 | 4 | 4.5
8879 | 5 | 1.5

Can it be done via SQL?

Solution:

Can you get an EXPLAIN plan?

EXPLAIN select d.HFID, d.TID, g.grade
from tblDetails d, (
select d.HFID, sum(m.DPI) as Grade
from tblDetails d
inner join tblMain m on m.TID = d.TID
group by d.HFID
) g
where d.HFID = g.HFID
;

This will help find issues.  Might be no index on HFID or TID columns.

But before we go too far down the path, might be good to understand why you are storing this value in the first place.  Does NOT fully normalization as you are storing a calculated value.  The next time you add a row for 1040, your grade is now incorrect and this routine would have to be re-run.

Are you sure you don’t want to remove the Grade column from the table and instead have a view that does what I have show above (minus the keyword EXPLAIN) or create a view if you are going to use this often and use a simple:

select d.HFID, sum(m.DPI) as Grade
from tblDetails d
inner join tblMain m on m.TID = d.TID
group by d.HFID
;

As the Grade is the same regardless of TID, so is that really needed in the final selection.  Plus even if it is, you can use group_concat() in MySQL like this:

select d.HFID, sum(m.DPI) as Grade
, group_concat(d.TID) as TIDs
from tblDetails d
inner join tblMain m on m.TID = d.TID
group by d.HFID
;

Anyway, if you HAVE to do the update, take a look at the EXPLAIN plan and the system you are running this on (phyical memory, MySQL memory configuration – my.cnf, etc.) to ensure that you have enough resources to support the number of rows you are working with.

–isa