Update all rows with a one to many relationship count in the same table in MYSQL



  • I am trying to update all rows of a MYSQL table with the total amount of referred users for each user. 1 user can refer multiple users and I need to run a MYSQL query to update the invite count (total amount of users they have referred) on all rows.

    Columns:

    • id
    • referred_by_user_id
    • invite_count

    This is what I came up with but it is not working (returns 0 on every row). What is the best way to accomplish this query?

    SET @code=0;
    UPDATE users
        SET invite_count =
           (SELECT count(*)
               where referred_by_user_id = @code:=@code+1);
    

    Table Schema

    CREATE TABLE `users` (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `referred_by_user_id` bigint DEFAULT NULL,
      `invite_count` int NOT NULL DEFAULT '0',
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    



  • my production server is running 10.3.31-MariaDB however I can switch it to MYSQL 8 if needed. – webmaster8800

    This solution is applicable for both MySQL 8+ and MariaDB 10.3+

    UPDATE users t1
    JOIN ( SELECT referred_by_user_id, COUNT(*) cnt
           FROM users t2
           GROUP BY 1 ) t3 ON t1.id = t3.referred_by_user_id
    SET t1.invite_count = t3.cnt;
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mariadb_10.3&fiddle=beae1375004f1b0361ba31d54e3feeac




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2