1

Trying to write an table update statement in my bash script but gives me a syntax error mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

mysql -u UserName --password=MyPassword -D MyDatabase -e 'UPDATE MyTable SET name = SomeName WHERE number = someNumber ;'

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SomeName WHERE number = someNumber' at line 1

dwt.bar
  • 39
  • 7

1 Answers1

1

So the answer is: you must escape "" like so \" one liner to update mysql db from shell:

mysql -u userName --password=yourPassword -D databaseName -e "UPDATE tableName SET columnName = \"${variable}\" WHERE numberColumn = \"${numberVariable}\""
dwt.bar
  • 39
  • 7
  • 1
    Sorry, I put two single quotes together, so it looks like a double quote. Please use single quotes in MySQL queries. – Vlad L Nov 30 '20 at 00:39
  • https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – Vlad L Nov 30 '20 at 00:40
  • @VladL normally yes, but this is bash script so if you put single quotes over ${variable} it will be treated like a string, my version worked for me. reference for single quotes in bash: https://www.howtogeek.com/howto/29980/whats-the-difference-between-single-and-double-quotes-in-the-bash-shell/ – dwt.bar Nov 30 '20 at 00:52
  • hmmm, the outer double-quotes should be sufficient to allow variable expansion (for variables referenced between the double-quotes) while also allowing for single quotes around the SQL strings, eg, `"UPDATE ..... columnName = '${variable}' WHERE numberColumn = '${numberVariable}'"` .. or does mariadb require double-quotes around literal values? – markp-fuso Nov 30 '20 at 03:30