Adding a count to a command line query

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.
LVL 6
rjohnsonjrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.