Solved

Drupal: DELETE users that match a certain role with MySQLAdmin

Posted on 2010-11-10
6
895 Views
Last Modified: 2012-05-10
Consider my limited experience with this, and here's the "idea" of what I want to do:

DELETE FROM `users` INNER JOIN `users_roles` ON `users.uid` = `users_roles.uid` WHERE `users_roles.rid` (7, 5, 6);

I don't think that's right, because I get a syntax error, and this is nothing I want to be unsure about, of course.

* MySQL client version: 5.1.41
* Drupal 6.19

Thank you
0
Comment
Question by:Quevin
6 Comments
 
LVL 3

Expert Comment

by:dnadavis
ID: 34108919
try this
DELETE FROM users   WHERE uid IN (SELECT ur.uid FROM users_roles  WHERE  rid IN (7,5,6))
0
 
LVL 2

Expert Comment

by:mobiusNZ
ID: 34108958
How about

DELETE FROM `users` WHERE `users.uid` IN (SELECT `uid` FROM `users_roles` WHERE `rid` = (7, 5, 6))

0
 

Author Comment

by:Quevin
ID: 34109150
I ended up deleting each rid:

DELETE FROM drupal.users USING drupal.users INNER JOIN drupal.users_roles ON drupal.users.uid = drupal.users_roles.uid WHERE drupal.users_roles.rid = 7

DELETE FROM drupal.users_roles WHERE drupal.users_roles.rid = 7

Looks like both of the queries would combine this?
0
 
LVL 3

Accepted Solution

by:
dnadavis earned 500 total points
ID: 34109236
ok you need to delete the record corresponding to the rid 7 in both the tables users and users_roles right.
then you can use this query.
DELETE  u.*,ur.*  FROM users  u,users_roles ur  WHERE  u.uid = ur.uid AND  ur.rid = 7
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37283995
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join tables 4 57
How to update the value of duplicated records (except latest one) 2 28
remote mysql 8 40
reverse engineer .sql from php files 11 54
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question