ima747 ima747 - 4 months ago 10
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.

Answer

Here's the query:

UPDATE
your_table YT
INNER JOIN 
(
  SELECT 
   content,
   COUNT(*) total
  FROM your_table
  GROUP BY content
) AS t
ON YT.content = t.content
SET YT.repeated = t.total;

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

Explanation:

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 t.total.

Comments