Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Update from Select Count

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    3

    Wow, quick reply

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have to use a 2-step process like i showed you

    mysql will not let you update the same table you're selecting from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    3

    appreciate your help

    Thanks for that. I guess that explains why I have been having SO much difficulty with this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •