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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mark BullockQA EngineerCommented:
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
Mark BullockQA EngineerCommented:
You're trying to update values in two tables with one update statement.
Update each table in a separate update statement.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

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
Mark BullockQA EngineerCommented:
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
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.