Hello again list, Thanks for pointing out where I was making my mistake. I just needed to select the right field. And this is just a test environment so getting rid of those users won't have any meaningful impact. Also previewing what you will be deleting by using a select is great advice I intend to use.
Best tim ----- Original Message ----- From: "Paul DuBois" <paul.dub...@oracle.com> To: "Tim Dunphy" <bluethu...@jokefire.com> Cc: mysql@lists.mysql.com Sent: Saturday, January 14, 2012 6:46:38 PM Subject: Re: delete all hosts using a wildcard On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > hello list, > > I have a number of hosts that I would like to delete using a wildcard (%) > symbol. > > Here is the query I am using: > > mysql> delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html > Query OK, 0 rows affected (0.00 sec) > > And I am attempting to delete all the hosts at the domain > 'summitnjhome.com'... > > But as you can see I am unsuccessful: > > mysql> select user,host from mysql.user; > +----------+-----------------------------+ > | user | host | > +----------+-----------------------------+ > | root | 127.0.0.1 | > | repl | virtcent10.summitnjhome.com | > | admin | virtcent11.summitnjhome.com | > | repl | virtcent19.summitnjhome.com | > | repl | virtcent23.summitnjhome.com | > | repl | virtcent30.summitnjhome.com | > +----------+-----------------------------+ > > > I know I can delete them individually and this is what I am going to do. But > I would like to use this as a learning opportunity to help me understand how > the wildcard works. > > Thanks in advance.. > > Best regards, > Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
