CODEWITHSUNDEEP
mysqlsql
MIYUKI NARAHARA
MIYUKI NARAHARA

Reputation: 121

Count duplicate columns and update the counter column

Report table Columns:
ID | ipaddress | count | category

I have duplicate ipaddress entries, I want to count the number of each IP and update the count column for each IP.

I'm able to count the duplicates like so

SELECT ipaddress, COUNT(*) FROM report GROUP BY ipaddress HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

Output 

 ipaddress       COUNT(*) 
192.168.2.187        16
192.168.6.14         14
192.168.2.17         11
3192.168.3.6         9
192.168.2.77         8

I just need to update each ip with its correct count number.

Upvotes: 0

Views: 51

Answers (1)

MarcinJ
MarcinJ

Reputation: 3639

You can JOIN in an UPDATE, like so:

UPDATE report r
  JOIN (SELECT ipaddress, COUNT(*) cnt FROM report GROUP BY ipaddress) up
    ON up.ipaddress = r.ipaddress
   SET r.`count` = up.cnt

Upvotes: 1

Related Questions