select query with count inside an update query

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

Transact-SQL (2000)

select query with count inside an update query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-12 : 09:26:10

Ian writes "I have this update query that is giving 'Operation must use an updateable query'

UPDATE tblFFs SET tblFFs.FSrptTopHoldingHeight = (SELECT Count(tblTopHoldings.FundCode) as CNT
FROM tblTopHoldings
WHERE tblTopHoldings.FundCode=tblFFs.FundCode
GROUP BY tblTopHoldings.FundCode)

any ideas

Thanks
Ian

SQL 7, Windows 2000 SP4"

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-12 : 23:14:21

It should word




UPDATE tblFFs SET tblFFs.FSrptTopHoldingHeight = A.CNT
FROM tblFFs t
INNER join
	(
	SELECT FundCode, Count(FundCode) as CNT
	FROM tblTopHoldings 	
	GROUP BY FundCode) A ON A.FundCode=t.FundCode

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources