MySQL - Update table field from query in one statement

Question

I successfully managed to retrieve users with 2 or less posts from a database and display the result of that query which looks like this:

SELECT Users.*, count( Posts.user_id ) 
FROM Posts LEFT JOIN Users ON Users.user_id = Posts.user_id
GROUP BY Posts.user_id
HAVING count( Posts.user_id ) < 2

What i would like to achieve next is to update a field called active for all users of that query, so i tried to add an UPDATE statement together with the first query:

UPDATE Users
SET Users.Active = 3
WHERE count( Posts.user_id ) < 2

But i can't get it to work. Where should the UPDATE part be positioned or maybe should it be run separately ?

Dusan Radovanovic · Accepted Answer · 2019-01-13 03:03:21Z

1

In where statement you need to select count of posts per user and check if it is less than 2.

    UPDATE Users
    SET Users.Active = 3
    WHERE (SELECT count( Posts.user_id ) 
           FROM Posts 
           WHERE Users.user_id = Posts.user_id
           GROUP BY Posts.user_id) < 2

answered Jan 13, 2019 at 3:03

Dusan Radovanovic

1,67915 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Kerby Over a year ago

Worked like a breeze, thanks a lot for the fast answer Dusan!

Collectives™ on Stack Overflow

MySQL - Update table field from query in one statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related