Update from Select Count

daraghk · #1 (**permalink**) 05-11-09, 10:55

Posted - 05/07/2009 : 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 thecount
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 thecount 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

r937 · #2 (**permalink**) 05-11-09, 11:04

Code:

CREATE TABLE itemcounts
SELECT items.name, Count(items.toy) AS countoftoys
FROM items
GROUP BY items.name;

UPDATE items
INNER
  JOIN itemcounts
    ON itemcounts.name = items.name
   SET items.thecount = itemcounts.countoftoys;

daraghk · #3 (**permalink**) 05-11-09, 11:11

This however does not give the result I am looking for as it involves sending the results to a 'New' table. I am looking to have the results stored in the original table in the 'thecount' column.

I can achieve this by using your suggested method and adding another step to update the items table but I am trying to avoid having that 2 steps process and having that intermediate temporary table