Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to execute a sql command in bash, and do something based on whether or not there was a result.

e.g.

$ LOOKUP=123; mysql -e \
    'SELECT id FROM table WHERE id='$LOOKUP dbname \
    && echo "Row $LOOKUP exists"
    || echo "Row $LOOKUP does not exists"

I can think of bodges (e.g. testing for ZLS) but wondered if there was a clean way?

share|improve this question
    
Even if you find a solution that technically works this really, really feels like hitting a nail with the wrong hammer. If you're familiar w/ shell scripting I imagine you have another scripting language up your sleeve. Perl? Python? Php? Use their libraries for a more elegant solution. –  atxdba Feb 2 '12 at 3:54
    
Agreed! But this was a one-off quick data fix. (see my comment to @RolandoMySQLDBA below) –  artfulrobot Feb 2 '12 at 10:23

1 Answer 1

up vote 1 down vote accepted

Try changing the query to force a value

Instead of

SELECT id FROM table WHERE id=${LOOKUP}

you can use

SELECT COUNT(1) FROM table WHERE id=${LOOKUP}

Here is bash script to use that new query

LOOKUP=123
SQLSTMT="SELECT COUNT(1) FROM table WHERE id=${LOOKUP}"
RCOUNT=`mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}"`
if [ ${RCOUNT} -eq 0 ]
then
    echo "Row ${LOOKUP} exists"
else
    echo "Row ${LOOKUP} does not exist"
fi

Here is an alternative

LOOKUP=123
SQLSTMT="SELECT CONCAT('Row ',rcount,' ',IF(rcount=0,'Exists','Does Not Exist')) FROM (SELECT COUNT(1) rcount FROM rolando WHERE id=${LOOKUP}) A"
mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}" > /tmp/answer.txt
cat /tmp/answer.txt

Give it a Try !!!

share|improve this answer
    
Thanks. I went for something very similar: while read i ; do if [[ -z $(mysql_one -A -B -N -e "SELECT id FROM myTable WHERE id="$i") ]]; then echo "$i" ; fi; done <ids-list >ids-not-found –  artfulrobot Feb 2 '12 at 10:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.