July 11, 2007 at 4:19 pm
Hello,
I have a table like this:
ID Count
----------------
123 null
123 null
234 null
234 null
234 null
I want to update this table with count of each ID. The result I want is:
ID Count
----------------
123 2
123 2
234 3
234 3
234 3
Can someone tell me a quick way of achieving this?
I can think of two options:
1. writing a cursor and updating it
2. selecting the count into a temp table and updating it using join :
select id, count(id) into #temp from tablex group by id
update tablex set count = b.count from tablex a, #temp b where a.id=b.id
Which one would be more efficient?
Is there a better way to do this?
Thanks
July 11, 2007 at 4:30 pm
Replace the table with a view.
_____________
Code for TallyGenerator
July 11, 2007 at 4:33 pm
Yes, as Sergiy suggests a view would be your best bet. If you must continue with updating the count column there's no need for a cursor or a temp table. Try this:
DECLARE @Table TABLE (ID int, [Count] int)
INSERT INTO @Table
SELECT 123, NULL UNION ALL
SELECT 123, NULL UNION ALL
SELECT 124, NULL UNION ALL
SELECT 124, NULL UNION ALL
SELECT 124, NULL
SELECT *
FROM @Table
UPDATE t1
SET t1.[Count] = t2.IDCount
FROM @Table t1
INNER JOIN (
SELECT ID, COUNT(*) as IDCount
FROM @Table
GROUP BY ID
) t2
ON t1.ID = t2.ID
SELECT *
FROM @Table
July 11, 2007 at 6:27 pm
Thank you Rowan. It solves my issue.
Can you explain how a view will get it done?
Thanks
July 11, 2007 at 6:30 pm
DROP TABLE YourTableName
CREATE VIEW dbo.YourTableName
AS
SELECT ID, COUNT(*) as IDCount
FROM dbo.SourceTable
GROUP BY ID
GO
SELECT * FROM dbo.YourTableName
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy