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