While wrestling with a software upgrade to the LAMP application running this website, I wanted to test a fix for an issue I was experiencing. I would have thought this could be accomplished in straight MySQL, but a few searches including the official docs on the regexp and replace functions suggest it is not. When the best advice I could find was to do it in perl. Rather than bust out the old DBI routines, I decided to just use my shell.


[code='Bash']
#!/bin/bash
# migrate-maps.sh - Search and Replace on all rows in a MySQL table using bash
# I thought this would fix a bug but it does not
# see http://dev.jaws-project.com/ticket/1228

DBNAME=jaws26a_upgrade


COUNT=`mysql -Ne "SELECT max(id) from $DBNAME.jaws_url_maps;"`;

for i in `seq 1 $COUNT` ; do
OLD=`mysql -Ne "SELECT map from $DBNAME.jaws_url_maps WHERE id = $i;"`;
if [[ $OLD == "0" ]] ; then
continue;
fi
NEW='{site_url}/'$OLD;
mysql -Ne "UPDATE $DBNAME.jaws_url_maps SET map=\"$NEW\" WHERE id=$i;";

[/code]