Simple SUM Aggregate Fuction Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having an issue with an aggregate function in line 6 of the code below.
The error is: "An aggregate may not appear in the set list of an UPDATE
statement."

How can i SUM this value successfully? Thanks experts.

CREATE Trigger AvailableLogFootage
on tblLogs
FOR INSERT
As
UPDATE tblLogs
Set UnDesignatedFootage = Sum(tblBundles.CurrentFootage)
FROM tblLogs
INNER JOIN tblBundles ON tblLogs.FlitchNum = tblBundles.FlitchNum
WHERE tblBundles.IsDesignated = 'N'
 
Hey Mike,

You probably want to store the value to a variable then use that...

CREATE Trigger AvailableLogFootage
on tblLogs
FOR INSERT
As
DECLARE @TotalFootage int
SELECT @TotalFootage = SUM(tblBundles.CurrentFootage)
FROM tblBundles

UPDATE tblLogs
Set UnDesignatedFootage = @TotalFootage
FROM tblLogs
INNER JOIN tblBundles ON tblLogs.FlitchNum = tblBundles.FlitchNum
WHERE tblBundles.IsDesignated = 'N'

HTH,

John
 
John, You hit the nail on the head! Thanks!

John Spiegel said:
Hey Mike,

You probably want to store the value to a variable then use that...

CREATE Trigger AvailableLogFootage
on tblLogs
FOR INSERT
As
DECLARE @TotalFootage int
SELECT @TotalFootage = SUM(tblBundles.CurrentFootage)
FROM tblBundles

UPDATE tblLogs
Set UnDesignatedFootage = @TotalFootage
FROM tblLogs
INNER JOIN tblBundles ON tblLogs.FlitchNum = tblBundles.FlitchNum
WHERE tblBundles.IsDesignated = 'N'

HTH,

John
Click to expand...
 
Back
Top