Question 190925 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How loop through the rows for unique users and record the number of matches in another SQL table?

I recently started to study SQL and I’m trying to solve the problem:
I have a voting table db.votes, it records the nickname, time and type. Like an event log. It now has 500,000 lines.

I need to record the total number of matches for each user (his total number of votes) in another db.users table in the total_votes column.

I can get the total votes for a user manually, like:

SELECT COUNT(*) FROM voters WHERE username="john";

Also I can write down the received value in other table.

UPDATE users SET total_votes = 20 WHERE username="john";

But how to do it automatically with a loop?

I tried to google similar methods and found several options with SQL-T, but I’m not sure if this will suit me.

Tags:

2

Answers


  1. 0

    You can use a correlated sub query for that, that would also work with multiple user

    CREATE tABLe users(username varchar(10), total_votes int)

    CREATE TABLE voters(username varchar(10))

    UPDATE users 
  SET total_votes = (SELECT COUNT(*) FROM voters WHERE username = users.username) 
  WHERE username="john";

    Rows matched: 0  Changed: 0  Warnings: 0

    fiddle

    Login or Signup to reply.
  2. 0

    You can do this in a number of ways. One of them looks like this:

    with v (voter_id, vote_count) as
(
  select voter_id, count(*)
      from votes
      group by voter_id
)
 update users
  inner join v on v.voter_id = users.id
  set total_votes = v.vote_count;

    Here is a DBFiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search