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
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
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.
MySQL ServerShell ScriptingLinux
Last Comment
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.
Update each table in a separate update statement.
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Rather than closing the question because you found your bash problem, you can accept the answer which helped you.
ASKER
I can't accept my own solution.
ASKER
I figured out the problem by trial and error with punctuation.
I think the quote inside the apostrophes causes your problem.
where first like '"'%${first_name}%'