?
Solved

Update based on SUM columns

Posted on 2011-04-26
5
Medium Priority
?
517 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:BrookK
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 35472550
UPDATE a
SET flag = 1
FROM tbl1 a
inner join (
select a.id, a.locid ,SUM(b.Time1 + a.Time2) tot
from tbl1 a
inner join tbl2 b
on a.ID = b.ID
AND a.LocID = b.LocID
group by a.id, a.locid
having SUM(b.Time1 + a.Time2) > = b.TimeLimit  
 )b
on a.ID = b.ID
AND a.LocID = b.LocID

0
 
LVL 9

Expert Comment

by:radcaesar
ID: 35472554
AND SUM(b.Time1 + a.Time2) > = b.TimeLimit  
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35472698
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)

Open in new window

0
 
LVL 9

Expert Comment

by:kaminda
ID: 35472708
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)

Open in new window

0
 

Author Closing Comment

by:BrookK
ID: 35475703
Worked!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question