Change successful SQL Server Select with JOINS to an Update-CodePudding

I have a log file tblktime that contains all activity for 'Ourfile'. A second summary table tblktimefst (one record per ourfile) has been created to capture the first occurrence of particular codes in the log file and summarize the 'duration' column between the first record for that ourfile and when it appears in the tblktime log. (The first occurrence of 'LSF' is stored as fstLSFBtime.)

The Select statement that I have written accurately returns the unique id field 'Ourfile' and the correct sum as 'TotDur' that I would like to update back to tblktimefst.fstLSFDurSum

SELECT DISTINCT
    ktf.ourfile,
    SUM(CASE WHEN kt.btime <= ktf.fstlsfbtime THEN kt.duration ELSE 0 END) AS TotDur
FROM
    tblktime kt
INNER JOIN 
    tblktimefst ktf ON ktf.ourfile = kt.ourfile 
                    AND kt.btime <= ktf.fstLSFBtime
GROUP BY
    ktf.ourfile

SQL Server returns:

Ourfile	TotDur
47661	48
50265	48
36453	13
38270	54
39730	27
46156	190
37392	46
51905	25
and so on

The column that I'd like to update in tblktimefst is fstLSFDurSum for its unique 'Ourfile' record. I cant get the syntax correct to do so and hope I have provided enough info that someone may be able to assist.

If I can get the syntax correct for this one then I should be able to complete duration summaries between additional codes as well.

Thank you in advance.

CodePudding user response：

What about (untested):

UPDATE tblktimefst
SET fstLSFDurSum = TotDur
FROM tblktime t2
INNER JOIN
   (
   select distinct
   ktf.ourfile,
   SUM( CAse when kt.btime <= ktf.fstlsfbtime then kt.duration else 0 end) as TotDur
   from tblktime kt
   Inner JOIN tblktimefst ktf on ktf.ourfile = kt.ourfile and kt.btime <= ktf.fstLSFBtime
   group by ktf.ourfile) x ON x.ourfile = t2.ourfile