Adding  a count to a command line query

Posted on 2014-08-22
Last Modified: 2014-12-30
Here is a sample command line query:

E:\mysql\bin\mysql -urjohnsonjr -pMyHardPassword  -D%db% -h192.168.1.100 -P 3301  -e "delete from mlsv20master.mastertable where mlsnumber not in (select mlnumber from %db%.%mls%_allmlsnumbers) and mlsid=1;"  -v -v -v

Is there a way for me to add some kind of count to the query to make sure that %db%.%mls%_allmlsnumbers  has more than 1000 rows in it?

I was thinking about adding these types of queries to a stored procedure so I could use more logic, but it would be much better if I could add to the query or do something else right there in the command line query to check the count and not actually do the delete if something happens and the %db%.%mls%_allmlsnumbers is empty

What is happening is another part of the script fails  and the allmlsnumbers table ends up being blank so the delete statement wipes out all the data in the mastertable which is bad news.
Question by:rjohnsonjr
    1 Comment
    LVL 50

    Accepted Solution

    I have a few notes for you:

    1) Using your password hard-coded on the command line is insecure.  Only marginally better is using the my.cnf file to store it for this client.  MySQL 5.6 introduces the .mylogin.cnf files, which are much better for security.  Upgrading MySQL (if necessary) is a cakewalk.

    2) Deleting records should be a last resort type of scenario.  If you don't need the records, create a field for "is_deleted" and set it appropriately, or archive them to a separate table.  This small adjustment can save you from tons of grief when something unexpectedly goes wrong.

    3) If you are running multiple statements with interdependencies (e.g., your delete query depends on the query before it), you should consider using transactions on the process.  This is never more relevant than when your process has the potential to be destructive, and is prone to failure.

    4) It is very possible to retrieve a count based on the SELECT portion of your query: SELECT COUNT(*) FROM mastertable a LEFT JOIN allmlsnumbers b ON a.mlsnumber=b.mlnumber WHERE b.mlnumber IS NULL.  However, you cannot use flow logic in standard console statements.  You'll need to create a stored procedure to run the process, and call the SP from your command line.

    Overall, using a stored procedure is going to be much better for you.  You can incorporate logic to handle sanity checks before anything is done, giving you the opportunity for error checking/reporting, and the ability to halt the process if anything unexpected is encountered.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now