ima747 ima747 - 9 months ago 91
SQL Question

MySQL put repeat number in a field

I did this once before and I have zero idea how I accomplished it. This is part of a much bigger process so the why is not worth mentioning.

I have a column in a table that I need to store the number of repeats of the row. i.e.

Table currently looks like this

content | repeated
aaa | NULL
bob | NULL
aaa | NULL

Expected output is to have the table look like this

content | repeated
aaa | 2
bob | 1
aaa | 2

I can get those counts with distinct, or COUNT(*), but I can't for the life of me remember how to get a single query to run against the whole table and update all the records repeated field.


Here's the query:

your_table YT
   COUNT(*) total
  FROM your_table
  GROUP BY content
) AS t
ON YT.content = t.content
SET YT.repeated =;

SQL FIDDLE DEMO In demo you'll see data after being updated


First get count for each content by the inner select query and give it an alias t.

Then make an inner join between another alias of your table YT and t on matching content.

Now set the repeated field of YT table to the value from