Solved

Drupal: DELETE users that match a certain role with MySQLAdmin

Posted on 2010-11-10
6
883 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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect to a remote mysql server using C#? 9 75
Closing database connection after prepared statement 1 54
Insert data into database 2 45
mysql between clause 2 25
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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