bash update mysql from bash command

Question

I want to update database from bash command:

#!/bin/bash
list= cat /home/wwwroot/list.txt;
mysql -u root -D dbname -e "UPDATE wp_postmeta SET meta_value = '$list' WHERE meta_key = 1";

cat /home/wwwroot/list.txt
text1
text2
text3
...

How to $list variable can be read in sql query?

Thank you.

You'll need to provide more sample data so we understand exactly what you mean by "update database from cat command", but you may find my answer stackoverflow.com/a/8649732/620097 to give you a place to start on working with SQL and shell scripting. Good luck. — shellter
– shellter, Commented Nov 26, 2017 at 4:45
If the file is on the same host where the MySQL Server is running, you can do this more easily and more securely with the LOAD_FILE() function. — Bill Karwin
– Bill Karwin, Commented Nov 26, 2017 at 4:47

sjsam · Accepted Answer · 2018-01-17 07:13:45Z

3

Cat is an overkill here.

I suggest using an array if you need space delimited values.

list=($(</home/wwwroot/list.txt))
mysql -u root -D dbname -e "UPDATE wp_postmeta \
SET meta_value = '${list[@]}' WHERE meta_key = 1";

The advantage here is if you need comma separated values then you could employ bash [ parameter substitution ] to achieve your feet. Just change the query to

mysql -u root -D dbname -e "UPDATE wp_postmeta \
SET meta_value = '${list[@]/%/,}' WHERE meta_key = 1";
#Note ${list[@]/%/,} appends comma after each value in the text file

All good :-)

edited Jan 17, 2018 at 7:13

answered Nov 26, 2017 at 7:21

sjsam

22k6 gold badges63 silver badges114 bronze badges

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

Collectives™ on Stack Overflow

bash update mysql from bash command

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related