CODEWITHSUNDEEP
mysqlbashshellinsert
Grant
Grant

Reputation: 1337

Updating data in mysql db from bash script

It's my first time trying to insert some info in a mysql database with an executable .sh file.

I'm using the following bash script but it's not working. Obviously most of the vars below have been replaced and simplified for ease of understanding.

#!/bin/bash

mysql -D mydbname -u mydbuser -p mydbpass <<EOF
INSERT INTO mytable (mycolumn) VALUES ('myvalue') WHERE id = '13';

exit;

You can see that I only want to insert my value in the row where id = 13 and this row does exist.

I don't think i'm formatting the query properly am I?

EDIT : Ok after suggestions below i've now got this but it still doesn't work?

#!/bin/bash
mysql -D mydbname -u mydbuser -p mydbpass <<EOF
UPDATE mytable SET mycolumn = 'myvalue' WHERE id = '13';
exit;

Upvotes: 8

Views: 36991

Answers (4)

dwt.bar
dwt.bar

Reputation: 49

This is what worked for me as well: escape the quote like so \"

mysql -u userName --password=yourPassword -D databaseName -e "UPDATE tableName SET columnName = \"${variable}\" WHERE numberColumn = \"${numberVariable}\""

Upvotes: 0

Bouramas
Bouramas

Reputation: 1168

Your query doesn't work as you have not typed EOF at the end of the script. See my example below. 

#!/bin/bash

mysql -u root -ppassword DB_NAME <<EOF
SELECT * FROM CUSTOMERS WHERE NAME='YOURNAME';
SELECT * FROM ANOTHER_TABLE WHERE SOMETHING_ELSE;
... more queries
EOF

Note that the -ppassword argument can be skipped if the connection does not require a password.

Upvotes: 3

hari
hari

Reputation: 41

The solution works like a charm as specified in the above post. You could also use the -D option while specifying the dbname.

#!/bin/bash

mysql -u username -puserpass -D dbname -e "UPDATE mytable SET mycolumn = 'myvalue' WHERE id='myid'";

For additional reference on the options, refer below link: https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/

Upvotes: 4

Grant
Grant

Reputation: 1337

Found the answer after some trial and error.

Just in case anybody else is looking to do the same thing it's :

#!/bin/bash

mysql -u username -puserpass dbname -e "UPDATE mytable SET mycolumn = 'myvalue' WHERE id='myid'";

Note that there is no space between the -p and your password -puserpass if you put a space there -p userpass it will prompt you for the password.

Hope it helps somebody ;)

Upvotes: 16

Related Questions