update statement - SQL Server Forums

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)

update statement

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-12 : 03:16:26

hi,
I am trying to form a update query:
update @table1 set mcount=(select count(col1)from table2 where table2.col2=@table2.col2)
where @table1.col2=table2.col2

where @table1 is a variable of table datatype.
can anyone help me with this query?
thanks a lot.
regards,
harshal.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-12 : 03:38:43

For updates using aggregates, I always prefer to use a GROUP BY with a derived table and an INNER JOIN:


UPDATE table1
SET mcount = MyCount 
FROM table1
INNER JOIN
(SELECT t1.ID, COUNT(t2.col1) AS MyCount
FROM table1 t1 INNER JOIN table2 t2
ON t1.ID = t2.ID) SubQuery
ON table1.ID = SubQuery.ID

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-12 : 04:04:40

quote:
Originally posted by mohdowais

For updates using aggregates, I always prefer to use a GROUP BY with a derived table and an INNER JOIN:

UPDATE table1
SET mcount = MyCount
FROM table1
INNER JOIN
(SELECT t1.ID, COUNT(t2.col1) AS MyCount
FROM table1 t1 INNER JOIN table2 t2
ON t1.ID = t2.ID) SubQuery
ON table1.ID = SubQuery.ID

OS

Got it.

Thanks a lot.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources