Iterate over SQL results in bash script. Is there a better way to do this?

word_virus · 11-07-2008, 07:05 PM

Hey, all.

So, I've got a shell script that I'm using to update a table in a MySQL database. It runs a select, reads in the results one line at a time, and uses them as params in an update statement. I'm using AWK to grab the fields I need from the output of the select and put them in the proper order in the update statement. Which works -- but it feels like a hack. Is there a better way to do this?
Example follows:

Code:

/usr/bin/mysql --skip-column-names -u$SQL_USR -p$SQL_PASS -D$SQL_DB -e"SELECT id,name FROM $SQL_TABLE" |
while read FN; do
echo "UPDATE users SET foo = bar WHERE id = `echo $FN | awk '{ print $1 }'` and name = `echo $FN | awk '( print $2 }'`"
done

Thanks in advance for your ideas.

graemef · 11-07-2008, 10:56 PM

you can do a subquery insert...

INSERT INTO users (id, name) (SELECT id,name FROM $SQL_TABLE)

graemef · 11-07-2008, 11:04 PM

IN MySQL you can use IGNORE to ignore duplicates or ON DUPLICATE UPDATE ... to update the value, thus you would have something like:

Code:

INSERT INTO users (id, name) 
   SELECT $SQL_TABLE.id,$SQL_TABLE.name
   FROM $SQL_TABLE 
   ON DUPLICATE KEY UPDATE user.id=$SQL_TABLE.id, user.name = $SQL_TABLE.name

jan61 · 11-09-2008, 09:52 AM

Moin,

Quote:

Originally Posted by graemef

...

Code:

INSERT INTO users (id, name) 
   SELECT $SQL_TABLE.id,$SQL_TABLE.name
   FROM $SQL_TABLE 
   ON DUPLICATE KEY UPDATE user.id=$SQL_TABLE.id, user.name = $SQL_TABLE.name

But this will perform an insert on non existing rows - the original post did not do it.

@graemef: At least you should avoid calling awk twice for each line of your input. You can do the whole work in one awk:

Code:

/usr/bin/mysql ... | awk ' \
  { printf "UPDATE users SET foo = bar WHERE id = %d and name = \'%s\'\n",
           $1, $2; } '

Jan

word_virus · 11-09-2008, 10:15 AM

Quote:

Originally Posted by jan61

Code:

/usr/bin/mysql ... | awk ' \
  { printf "UPDATE users SET foo = bar WHERE id = %d and name = \'%s\'\n",
           $1, $2; } '

That does look a little less ugly. Thank you.