Updating data in mysql db from bash script

Question

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;

The query itself is wrong. INSERT doesn't have WHERE clause — a1ex07
– a1ex07, Commented Feb 11, 2015 at 19:05
Doh! Of course you are right.. Major oversight on my part.. Feel very silly now :( — Grant
– Grant, Commented Feb 11, 2015 at 19:08

Grant · Accepted Answer · 2015-02-11 20:32:42Z

16

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

answered Feb 11, 2015 at 20:32

Grant

1,3372 gold badges17 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

symcbean Over a year ago

The heredoc method will work as well if it uses proper SQL and bash syntax. But both that approach and the one above are rather inefficient when you want to execute multiple statements - and it won't work for transactions. In such scenarios, piping the output of a bash script to mysql's stdin will be more efficient and support transactions.

jww Over a year ago

@symcbean, re: "piping the output of a bash script to mysql's stdin will be more efficient and support transactions". Perhaps you should provide an example.

hari · Accepted Answer · 2017-04-06 14:41:09Z

4

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/

answered Apr 6, 2017 at 14:41

hari

411 bronze badge

Comments

Bouramas · Accepted Answer · 2017-11-03 14:09:03Z

3

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.

answered Nov 3, 2017 at 14:09

Bouramas

1,1881 gold badge18 silver badges38 bronze badges

Comments

dwt.bar · Accepted Answer · 2020-11-30 01:01:55Z

0

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}\""

answered Nov 30, 2020 at 1:01

dwt.bar

497 bronze badges

Collectives™ on Stack Overflow

Updating data in mysql db from bash script

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related