Update statement using Count function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update statement using Count function

Author

Topic

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 13:10:50

Hi all,
I've been getting error on the update statements below, I tried different ways but still couln't get it worked. Any help will be greatly appreciated.
The error is :
" MSG 147, ... 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. "

UPDATE tableA SET ApptCode=@ApptCode
FROM tableB INNER JOIN tableC ON tableB.MemberID = tableC.MemberID INNER JOIN tableA ON tableB.MemberID = TableA.MemberID
WHERE (TableA.Apptcode = 'PE') AND (TableA.TrpOther = 1) and TableB.Year = '2004' AND COUNT(tableC.RefID) >= 2

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-04 : 13:19:16

what are you trying to count? you are not doing any grouping or summarizing so even if that did compile, it would always return 1.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 13:30:27

This a TOTAL WAG


  UPDATE a 
     SET ApptCode=@ApptCode 
    FROM tableB b 
    JOIN tableC c 
      ON b.MemberID = c.MemberID 
    JOIN tableA a 
      ON b.MemberID = a.MemberID 
   WHERE a.Apptcode = 'PE'
     AND a.TrpOther = 1 
     AND b.[Year] = '2004' 
GROUP BY a.MemberId   
  HAVING COUNT(c.RefID) >= 2

Brett

8-)

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 13:34:41

the relationship of these tables:

TableA to TableB is one to one
TableA to TAbleC is one to many

I need to update the TableA.ApptCode only if TableA.Apptcode='PE' and TableA.TrpOther='1' and TableB.Year='2004' and (there are atleast 2 records for each matching Memberid in TableC)

Examples:
TableA: MemberID: A1, ApptCode: PE, TrpOther: true
MemberID: A2, ApptCode: PE, TrpOther: true
TableB: MemberID: A1, Year: 2004
MemberID: A2, Year: 2004
TableC: RefID: 1, MemberID: A1
RefID: 2, MemberID: A1
RefID: 3, MemberID: A2
RefID: 4, MemberID: A3
The result is only record for TableA.MemberID = A1 get updated

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 13:42:51

I tried Brett's way but it gave me this error:
Error# 156Incorrect syntax near the keyword 'GROUP'

I just wonder if you can use WHERE and HAVING in the same group?

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 14:15:25

Let's just say this...it's alot easier if you post it like this....


USE Northwind
GO

CREATE TABLE tableA(MemberId char(2), ApptCode char(2), TrpOther int)
CREATE TABLE tableB(MemberId char(2), [Year] char(4))
CREATE TABLE tableC(MemberId char(2), RefID int)
GO

INSERT INTO TableA(MemberID,ApptCode, TrpOther)
SELECT 'A1', 'PE', 1 UNION ALL 
SELECT 'A2', 'PE', 1

INSERT INTO TableB (MemberID, Year)
SELECT 'A1', '2004' UNION ALL
SELECT 'A2', '2004'

INSERT INTO TableC (RefID, MemberID)
SELECT 1, 'A1' UNION ALL
SELECT 2, 'A1' UNION ALL
SELECT 3, 'A2' UNION ALL
SELECT 4, 'A3'
GO

DECLARE @ApptCode char(2)
SELECT @ApptCode = 'BK'

  UPDATE a 
     SET ApptCode=@ApptCode 
    FROM tableA a
    JOIN tableB b 
      ON a.MemberID = a.MemberID 
   WHERE a.Apptcode = 'PE'
     AND a.TrpOther = 1 
     AND b.[Year] = '2004'
     AND Exists (SELECT MemberId 
		   FROM tableC c 
		  WHERE a.MemberId = c.MemberId 
	       GROUP BY c.MemberId
		 HAVING COUNT(c.RefId) > 1
		)
GO
   
SELECT * FROM tableA
GO

DROP TABLE tableA
DROP TABLE tableB
DROP TABLE tableC
GO

Brett

8-)

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 14:39:40

Brett,
I'll try to work around to find some other shorter ways, if none of them won't work, I surely will use your method. Thanks very much.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 14:53:42

Did you cut and paste the example in Query Analyzer?

Brett

8-)

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-08-04 : 15:06:35

Oops

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 15:22:49

Brett,
I'm trying to run it now. Thanks for being so patient. I'll let you know the results

one5
Starting Member

6 Posts

Posted - 2004-08-04 : 15:30:40

It worked!

Thank you very much Brett! Now I can take a short break!

I cut this part from your codes above and pasted them in my aspx page and it worked perfectly.

UPDATE a
SET ApptCode=@ApptCode
FROM tableA a
JOIN tableB b
ON a.MemberID = a.MemberID
WHERE a.Apptcode = 'PE'
AND a.TrpOther = 1
AND b.[Year] = '2004'
AND Exists (SELECT MemberId
FROM tableC c
WHERE a.MemberId = c.MemberId
GROUP BY c.MemberId
HAVING COUNT(c.RefId) > 1
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources