Sql Doing Math Find Smallest Value And Then Insert Into Column

i have 2 table this is my first table +-----------+---------+------+------+---------+---------+-------------+ | name | squad | val1 | val2 | result1 | result2 | finalresult

Solution 1:

Checkout this http://sqlfiddle.com/#!9/e995b/1

select ad.* from (
SELECT name, a.squad, val1, val2, (val1+val3) as result1, (val2+val4) as result2, ((val1+val3)*(val2+val4)) as finalresult
FROM a
CROSS JOIN b ON a.squad=b.squad
) as ad
inner join ( 
SELECT name, min((val1+val3)*(val2+val4)) as finalresult
FROM a 
LEFT JOIN b ON a.squad=b.squad
groupby name,a.squad
 ) as f
 on ad.name = f.name and ad.finalresult = f.finalresult

Solution 2:

If you want to update the original table, then the logic would look like:

update table1 t1 join
       (select t1.*, t2.val3, t2.val4,
               (t1.val1 + t2.val3) * (t1.val2 + t2.val4) as finalresult,
               row_number() over (partition by t1.name, t1.squad orderby (t1.val1 + t2.val3) * (t1.val2 + t2.val4)) as seqnum
        from table1 t1 join
             table2 t2
             using (squad)
       ) tt
       on tt.name = t1.name and tt.squad = t1.squad and
          tt.val1 = t1.val1 and tt.val2 = t1.val2
    set t1.result1 = tt.val3,
        t1.result2 = tt.val4,
        t1.finalresult = tt.finalresult
    where tt.seqnum = 1;

Solution 3:

Do finalresult = MIN ((val1+val3) * (val2+val4)for every subsquad) for every row on a.squad= b.squad like you have done and rank each group of name/squad order by finalresult.

select a.name,a.squad,a.val1,a.val2,b.val3 as result1,b.val4 as result2,
  (a.val1+b.val3) * (a.val2+b.val4) as finalresult,
  row_number() over (partition by a.name,a.squad orderby (a.val1+b.val3) * (a.val2+b.val4)) 
  from a join b on a.squad=b.squad

Then you just select above table with condition rn=1.

select name,squad,val1,val2,result1,result2,finalresult from
  (select a.name,a.squad,a.val1,a.val2,b.val3 as result1,b.val4 as result2,
      (a.val1+b.val3) * (a.val2+b.val4) as finalresult,
      row_number() over (partition by a.name,a.squad order by (a.val1+b.val3) * (a.val2+b.val4)) 
      from a join b on a.squad=b.squad
  ) where rn=1

In case of the DB version is lower 8.

Just using @Mohtisham Zubair 's anwser.