MySql performing multiple accounts with different locations on the same table for the same user and the update

advertisements

I'm stuck with two things here, that need to be done in a single sql statement.

One, is to do four counts with different where conditions, on the same table, for the same user, and the second part is to update a different table with the data of those counts for the same user.

The whole thing has me in knots. I'm ok with basic update table select count(*) etc things, but how is this doable?

userData

"userId"    "postId"    "postStatus"    "postTitle"
"1"         "1"         "0"             "Title 1"
"1"         "2"         "0"             "Title 2"
"1"         "3"         "0"             "Title 3"
"1"         "4"         "0"             "Title 4"
"1"         "5"         "1"             "Title 5"
"1"         "6"         "1"             "Title 6"
"1"         "7"         "1"             "Title 7"
"1"         "8"         "6"             "Title 8"
"1"         "9"         "5"             "Title 9"
"1"         "10"        "5"             "Title 10"
"2"         "11"        "0"             "Title 11"
"2"         "12"        "0"             "Title 12"
"2"         "13"        "0"             "Title 13"
"2"         "14"        "1"             "Title 14"

userStats

"userId"    "published" "inactive"  "recalled"  "deleted"
"1"         "0"         "0"         "0"         "0"

My failed sql

update userStats set
published = select count (postStatus) published where postStatus = 0,
inactive = select count (postStatus) inactive where postStatus = 1,
recalled = select count (postStatus) recalled where postStatus = 5,
deleted = select count (postStatus) deleted where postStatus = 6
from userData where userId = 1;

Expected results

"userId"    "published" "inactive"  "recalled"  "deleted"
"1"         "4"         "3"         "2"         "1"

In the end, it works out to select count from userData for userId = 1 and update userStats with that data for that user. Can you please help?

Solution

update userStats us
join (select
  userId,
  sum(postStatus = 0) published,
  sum(postStatus = 1) inactive,
  sum(postStatus = 5) recalled,
  sum(postStatus = 6) deleted
from userData where userId = 1) d on d.userId = us.userId set
us.published = d.published,
us.inactive = d.inactive,
us.recalled = d.recalled,
us.deleted = d.deleted;


The only way this question makes sense is if all sample rows for userData are for userId 1, and that you want to update all users with their totals.

Firstly, here's the query for the data:

select
  userId,
  sum(postStatus = 0) published,
  sum(postStatus = 1) inactive,
  sum(postStatus = 5) recalled,
  sum(postStatus = 6) deleted
from userData
group by 1

Btw, the reason the totals work is that in mysql true is 1 and false is 0, so summing a condition counts how many times it's true! It makes the query very neat compared to long winded case statements.

Now to update another table, use mysql's special update-with-join syntax:

update userStats us
join (select
  userId,
  sum(postStatus = 0) published,
  sum(postStatus = 1) inactive,
  sum(postStatus = 5) recalled,
  sum(postStatus = 6) deleted
from userData
group by 1) d on d.userId = us.userId set
us.published = d.published,
us.inactive = d.inactive,
us.recalled = d.recalled,
us.deleted = d.deleted
where us.userId = 1 -- optional where clause. leave out to update all users

This will correctly update all users in one query.

This is quite efficient because it make one pass over the userData table (instead of 4 passes with separate subqueries) and all users have their stats refreshed (rather than separate queries for each user).

I you actually only want to update one particular user, just add a where us.userId = ? to the end of the query.

I would encourage you to consider using raw data rather than denormalizing the results into the userStats table, unless there's a very compelling reason. It's a form of caching, and caching comes with many hidden maintenance overheads and subtle problems.