Update from select Count - 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

General SQL Server Forums

New to SQL Server Programming

Update from select Count

Author

Topic

dkennedy
Starting Member

2 Posts

Posted - 2009-05-07 : 13:31:42

I am looking to update a column in a table with a count of duplicate entries. The example below will clarify as bit better

Table name - items

Name Toy Count
tom box
dave truck
tom block
mike car
dave house

The query should update the count field with the number of entries for tom, dave etc so for each entry for tom the count value will be 2, for dave will be 2 and mike will be 1.

I can get the count returned using the following query
SELECT items.name, Count(items.toy) AS countoftoys
FROM items
GROUP BY items.name;

I have been unable to take this to the next stage where the results are updated back into the count column

This is a simplified example of a real world application I have but is too complicated to get into.

Any insight anyone can provide will be much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-07 : 13:34:59

[code]
SELECT items.name,items.toy, Count(*) AS countoftoys
FROM items
GROUP BY items.name,items.toy
[/code]

dkennedy
Starting Member

2 Posts

Posted - 2009-05-07 : 16:48:00

The select is not a probles it is including the update and making the select a sub query. I need to be able to updat the result of the select all in one query

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-07 : 17:51:51

Maybe something like this?

UPDATE 
	I
SET 
	Count = MyCount
FROM 
	items AS I
INNER JOIN
	(
		SELECT Name, COUNT(*) AS MyCount
		FROM Items
		GROUP BY Name
	) AS T
	ON I.Name = T.Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:23:51

or this may be?


UPDATE t
SET t.Count=tmp.countoftoys
FROM Items t
INNER JOIN (SELECT items.name,items.toy, Count(*) AS countoftoys
FROM items
GROUP BY items.name,items.toy)tmp
ON tmp.Name=t.Name 
AND tmp.Toy = t.Toy

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-08 : 11:39:09

quote:

Originally posted by visakh16

or this may be?


UPDATE t
SET t.Count=tmp.countoftoys
FROM Items t
INNER JOIN (SELECT items.name,items.toy, Count(*) AS countoftoys
FROM items
GROUP BY items.name,items.toy)tmp
ON tmp.Name=t.Name 
AND tmp.Toy = t.Toy

That doesn't produce the correct results. The OP wanted: "each entry for tom the count value will be 2, for dave will be 2 and mike will be 1."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources