Need Syntax Assistance With SQL Statement in BASH Statement

if sql_string=$(sshpass -p $ldappw ssh $ldapuser@$ssh_host  'mysql --skip-column-names '${db_info}' -e \
        "update users u left join user_demographics ud on u.id = ud.user_id
        set u.password= md5 ('DoNotUse12345'), ud.last_password_date_changed='2011-01-01 00:00:00', ud.isAccountLocked=1 where u.user_id in select id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"'); then
         echo "Password, Last Password Changed Date, and Account Lock Status updated for ${first_name} ${last_name} in $environment"

   else
      echo $environment ": DATABASE CONNECTION FAILED"
   fi

Open in new window


I'm getting an error when running this query. I don't think the syntax is appreciated :-)

I've tried putting parentheses around the second select statement and got the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00, ud.isAccountLocked=1 where u.user_id in (select id from users where fi' at line 2

Open in new window


Without the parentheses I get the same error. I'm sure my puncutation is wrong somehow since this is bash. I'm just lost as to how to correctly use the needed syntax.
TampaJayAsked:
Who is Participating?
 
TampaJayAuthor Commented:
I just figured it out by trial and error. Following code works:

 if sql_string=$(sshpass -p $ldappw ssh $ldapuser@$ssh_host  'mysql --skip-column-names '${db_info}' -e \
        "update users u, user_demographics ud set u.password=md5 '"('DoNotUse12345'), ud.last_password_date_changed='2011-01-01 00:00:00', ud.isAccountLocked=1 where u.id=ud.user_id and u.first like '%${first_name}%' and u.last like '%${last_name}%';"'"'); then
         echo "Password, Last Password Changed Date, and Account Lock Status updated for ${first_name} ${last_name} in $environment"

   else
      echo $environment ": DATABASE CONNECTION FAILED"
   fi
0
 
Mark BullockQA Engineer IIICommented:
You need the parentheses for the select subquery following in.

I think the quote inside the apostrophes causes your problem.
where first like '"'%${first_name}%'
0
 
TampaJayAuthor Commented:
It's more than the subquery itself, buecause I also get an Error Code: 1093  You can't specify target table 'u' for update in FROM clause
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Mark BullockQA Engineer IIICommented:
You're trying to update values in two tables with one update statement.
Update each table in a separate update statement.
0
 
TampaJayAuthor Commented:
I've modified the query to where it returns correct results. Now my problem is bash related and getting the syntax to work correctly:

 if sql_string=$(sshpass -p $ldappw ssh $ldapuser@$ssh_host  'mysql --skip-column-names '${db_info}' -e \
        "update users u, user_demographics ud set u.password=md5 ('DoNotUse12345'), ud.last_password_date_changed='2011-01-01 00:00:00', ud.isAccountLocked=1 where u.id=ud.user_id and u.first like '"'%${first_name}%' and u.last like '%${last_name}%';"'"'); then
         echo "Password, Last Password Changed Date, and Account Lock Status updated for ${first_name} ${last_name} in $environment"
   else
      echo $environment ": DATABASE CONNECTION FAILED"
   fi

Open in new window

0
 
Mark BullockQA Engineer IIICommented:
Rather than closing the question because you found your bash problem, you can accept the answer which helped you.
0
 
TampaJayAuthor Commented:
I can't accept my own solution.
0
 
TampaJayAuthor Commented:
I figured out the problem by trial and error with punctuation.
0
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.

All Courses

From novice to tech pro — start learning today.