Solved

MySQL/Linux: Multiple Queries from BASH

Posted on 2011-09-08
2
644 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

0
Comment
Question by:hankknight
2 Comments
 
LVL 8

Accepted Solution

by:
pilson66 earned 250 total points
ID: 36503854
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
0
 
LVL 38

Assisted Solution

by:wesly_chen
wesly_chen earned 250 total points
ID: 36504608
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

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Fine Tune your automatic Updates for Ubuntu / Debian
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

829 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