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.
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);
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;