October 18, 2005 at 8:24 am
Receiving an Error trying to run the attached SQL:
Server: Msg 157, Level 15, State 1, Line 1
An aggregate may not appear in the set list of an UPDATE statement.
Update TABLE_A Set COL_1 = dateadd(day,+364,max(TABLE_E.SystemCreateDate)
From TABLE_E
inner join TABLE_A on TABLE_E.ApplicationId = TABLE_A.Id
where TABLE_A.COL_1 < '2005-10-31'
and TABLE_A.StatusId in (2,7,8)
Any suggestions as to HOW I can use MAX(TABLE_E.SystemCreateDate) in this statement?
October 18, 2005 at 9:07 am
Derived table to get the max() for each ID, then join to the derived table:
Update TABLE_A
Set COL_1 = dateadd(day, +364, dt.MaxCreateDate)
From Table_A
Inner Join
(
Select TABLE_A.Id, Max(TABLE_E.SystemCreateDate) As MaxCreateDate
From TABLE_E
Inner join TABLE_A
on TABLE_E.ApplicationId = TABLE_A.Id
Where TABLE_A.COL_1 < '2005-10-31'
And TABLE_A.StatusId in (2,7,8)
) dt
On TABLE_A.Id = dt.Id
October 20, 2005 at 7:54 am
Why are you using 364 as a constant?? Shouldn't that be DATEADD(D, -1, dateadd(YY, 1, Date))?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply