CODEWITHSUNDEEP
mysql
Kyle Cureau
Kyle Cureau

Reputation: 19366

Calculated updates in one query

This query works for updating the field this_count with a count of the people that a certain user ($user_id) invited:

UPDATE table_one SET invitor_count = 
  (SELECT count(*) FROM table_one
      WHERE invitor = 
         (SELECT invite_name FROM table_one WHERE user_id = $user_id)
  )
WHERE user_id = $user_id;

Can I update all invitor_counts in one query? How can I do that?

TABLE STRUCTURE

+---------+------------+------------+-------------+
| user_id | name       | invitor    | invite_name |
+---------+------------+------------+-------------+
|     215 | Susan B    | Alicia18   | Susan       |
|     217 | Alicia L   |            | Alicia18    |
+---------+------------+------------+-------------+

Upvotes: 0

Views: 69

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

UPDATE
      table_one AS t
  JOIN
      ( SELECT b.user_id, COUNT(*) AS cnt
        FROM 
              table_one a
          JOIN
              table_one AS b
            ON a.invitor = b.invite_name
        GROUP BY b.user_id
      ) AS grp
    ON grp.user_id = t.user_id

SET t.invitor_count = grp.cnt

Upvotes: 1

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7302

I think following query would help in Oracle, I'm not sure whether MySQL has NVL or there is some other function like it. 

update table_one z
   set invitor_count =
          nvl((select count(*)
                 from table_one x
                 join table_one y on x.invite_name = y.invitor
                where x.user_id = z.user_id), 0)

Upvotes: 2

Related Questions