BrookK
asked on
Update based on SUM columns
Hello All,
I have to update a column in a table based on sum of two column and the value of the sum if is >= third column then 1.
BEGIN TRAN
UPDATE a
SET flag = 1
FROM tbl1 a
inner join tbl2 b
on a.ID = b.ID
AND a.LocID = b.LocID
WHERE SUM(b.Time1 + a.Time2) > = b.TimeLimit
This update gives me an error.
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Can anyone tell me how to resolve this error?
Thanks,
-B
I have to update a column in a table based on sum of two column and the value of the sum if is >= third column then 1.
BEGIN TRAN
UPDATE a
SET flag = 1
FROM tbl1 a
inner join tbl2 b
on a.ID = b.ID
AND a.LocID = b.LocID
WHERE SUM(b.Time1 + a.Time2) > = b.TimeLimit
This update gives me an error.
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Can anyone tell me how to resolve this error?
Thanks,
-B
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
radcaesar
AND SUM(b.Time1 + a.Time2) > = b.TimeLimit
I would do something like this
UPDATE a
SET flag = 1
FROM tbl1 a
WHERE a.ID IN
(SELECT c.ID FROM tbl1 c INNER JOIN tbl2 d ON on c.ID = d.ID AND c.LocID = d.LocID GROUP BY c.ID,d.ID HAVING SUM(d.Time1 + c.Time2) > = c.TimeLimit)
Sorry code is truncated in the precvious post
UPDATE a
SET flag = 1
FROM tbl1 a
WHERE a.ID IN
(SELECT c.ID FROM tbl1 c
INNER JOIN tbl2 d ON on c.ID = d.ID AND c.LocID = d.LocID
GROUP BY c.ID,d.ID
HAVING SUM(d.Time1 + c.Time2) > = b.TimeLimit)ASKER
Worked!