Solved

MySQL/Linux: Multiple Queries from BASH

Posted on 2011-09-08

2 solutions

Medium Priority

653 Views

Last Modified: 2012-06-27

Using BASH on Linux I want to run the following MySQL queries:

# Host: abc.example.com
# Username: ujm
# Password: p123
# Database: ujm_d

DELETE a FROM Group_Members a inner join Member b ON a.MemberID = b.ID  WHERE (a.GroupID = '27' OR a.GroupID = '26' OR a.GroupID = '25' OR a.GroupID = '24' OR a.GroupID = '22' ) AND b.UserSuspended = '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '18', `ID` from `Member` WHERE `UserSuspended` = '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '27', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` = '1' AND `PremWebMember` = '1' AND `SMSMember` = '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '26', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` = '1' AND `PremWebMember` = '1' AND `SMSMember` != '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '25', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` = '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '24', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` != '1' AND `SMSMember` = '1'
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '22', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` != '1'

Open in new window

Comment

Question by:hankknight

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 8

Accepted Solution

by:pilson66

pilson66 earned 1000 total points

ID: 365038542011-09-08

echo "DELETE a FROM Group_Members a inner join Member b ON a.MemberID = b.ID WHERE (a.GroupID = '27' OR a.GroupID = '26' OR a.GroupID = '25' OR a.GroupID = '24' OR a.GroupID = '22' ) AND b.UserSuspended = '1'" | mysql -uujm -pp123 -Dujm_d

then the same principe for each query

LVL 38

Assisted Solution

by:wesly_chen

wesly_chen earned 1000 total points

ID: 365046082011-09-08

Usage:
mysql -u ujm -p p123 -h abc.example.com -D ujm_d -e "MYSQL QUERY ; "

mysql -u ujm -p p123 -h abc.example.com -D ujm_d -e "DELETE a FROM Group_Members a inner join Member b ON a.MemberID = b.ID WHERE (a.GroupID = '27' OR a.GroupID = '26' OR a.GroupID = '25' OR a.GroupID = '24' OR a.GroupID = '22' ) AND b.UserSuspended = '1'; "

mysql -u ujm -p p123 -h abc.example.com -D ujm_d -e "INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '18', `ID` from `Member` WHERE `UserSuspended` = '1' ; "

or combine then into one with ";" to separate them

mysql -u ujm -p p123 -h abc.example.com  -D ujm_d  [b]-e[/b] "INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '27', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` = '1' AND `PremWebMember` = '1' AND `SMSMember` = '1'; 
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '26', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` = '1' AND `PremWebMember` = '1' AND `SMSMember` != '1'; 
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '25', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` = '1' ;
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '24', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` != '1' AND `SMSMember` = '1' ; 
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '22', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` != '1' ; "

Open in new window

Featured Post

Question has a verified solution.

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

Master your MySQL data - Table partitioning approach for better performance.

Article by: Tomas Helgi

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…

Database Solutions Engineer FAQs

Article by: Percona

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…

Linux/ Unix Bash Shell: Getting Help

Video by: Dototot

Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…