-1

I have two tables that are in play here. A users table and a users_roles table. I need to update the users status column to 0 for almost all users except for a few, we can target these users by uid perhaps? Also the users that have a uid in the users_roles table should not be affected.

Table - users

  • Col - uid
  • Col - status

Table - users_roles

  • Col - uid
  • Col - rid

Any help would be appreciated.

Thanks!

  • Please consider reading this advice – mustaccio Jan 29 at 3:58
  • 1
    I finally figured it out. Trick was to check if the users_roles uid column is null. UPDATE users u LEFT JOIN users_roles ur ON u.uid = ur.uid SET status = 0 WHERE u.uid > 1 AND u.status <> 0 AND ur.uid is null AND u.uid <> 99; – Brian N Jan 29 at 4:25
0

I finally figured it out. Trick was to check if the users_roles uid column is null. UPDATE users u LEFT JOIN users_roles ur ON u.uid = ur.uid SET status = 0 WHERE u.uid > 1 AND u.status <> 0 AND ur.uid is null AND u.uid <> 99; – Brian N

Test

UPDATE users u 
SET u.status = 0 
WHERE u.uid > 1 
  AND u.uid <> 99
  AND u.status
  AND NOT EXISTS ( SELECT 1
                   FROM users_roles ur
                   WHERE u.uid = ur.uid );

Its performance may be higher.

|improve this answer|||||
  • Thank you @Akina that had much better performance. I also received a list of 16 users so I used: AND u.uid NOT IN ('2', '7', '8', '99', .....) which also made it more reliable. – Brian N Jan 29 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.