How to run an SQL Update string from bash script with parameter?

Question

I would like to be able to update a database field by simply running a script with the new value as a parameter.

ie> ./update.sh newvalue

and have it run an sql with the "newvalue" updating the cell.

Currently I open the database connection and paste in this sql to do the update.

UPDATE modules_config_x_roomtypes SET value = 'newvalue' WHERE modules_config_id = (SELECT id FROM modules_config WHERE module_id = (SELECT id FROM modules WHERE name = 'system') AND name = 'theme');

Have you checked Postgres documentation for psql? Here:postgresql.org/docs/current/static/app-psql.html — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 10, 2015 at 18:06

zzevannn · Accepted Answer · 2015-02-10 19:01:22Z

3

Something simple like this seems like it'd work unless I'm misunderstanding the need. Note I don't have psql so can't give you an exact solution but instead a guide.

#!/bin/bash

if [ $# -lt 1 ]
  then
    echo "USAGE: ./update.sh [newvalue]"
    echo "       [newvalue] represents the new value to insert into the DB."
    exit
fi

NEWVAL=$(echo $1 | sed "s/'/\\\'/g")
SQL="UPDATE modules_config_x_roomtypes SET value = '"$NEWVAL"' WHERE modules_config_id = (SELECT id FROM modules_config WHERE module_id = (SELECT id FROM modules WHERE name = 'system') AND name = 'theme');"
echo "$SQL" | mysql --user=user --password=password

This first makes sure you've got an argument, then escapes any single quotes in it so it doesn't break the SQL statement, then echo's the SQL to mysql. You'll have to modify the user and pass as req'd or source them from some other file.

If you need to insert a blank "" would work as the arg but this doesn't handle null.

You'll probably also want to add a check for sqls exit code to make sure the update took cleanly. That'd just be some simple if's with $?, but since that is not exactly in the context of the question I'll leave that as an exercise for you to complete.

I'm not running psql so I can't say the exact arguments needed for this to work there, but from the man page you can give the option -c to specify a commmand to run, so you'd probably replace the echo to mysql with that, and you'll also have to handle letting the script connect to the DB, but there are plenty of Q's on here around that.

edited Feb 10, 2015 at 19:01

answered Feb 10, 2015 at 18:52

zzevannn

3,8142 gold badges14 silver badges20 bronze badges

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

1 Comment

Drew Over a year ago

awesome thank you @zzevannn, this worked great with psql, i just added the db info to the end echo "$SQL" | psql wrkdb -U postgres

Collectives™ on Stack Overflow

How to run an SQL Update string from bash script with parameter?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related