Link to home
Create AccountLog in
Avatar of TampaJay
TampaJay

asked on

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.
Avatar of Mark Bullock
Mark Bullock
Flag of United States of America image

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}%'
Avatar of TampaJay
TampaJay

ASKER

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
You're trying to update values in two tables with one update statement.
Update each table in a separate update statement.
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

ASKER CERTIFIED SOLUTION
Avatar of TampaJay
TampaJay

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Rather than closing the question because you found your bash problem, you can accept the answer which helped you.
I can't accept my own solution.
I figured out the problem by trial and error with punctuation.