[SOLVED] How would I go about doing this...

trist007 · 02-25-2012, 05:39 PM

I have several records in a database and I want to modify the contents with a good line using awk. I want to grab the field in each record, extract some fields and then put the fields I extracted back into the database.

Here is what I have so far. I'm looking at the requirements field in the pc_t table and just grabbing the 1st, 3rd, and 6th fields from the 2nd line of that output, then putting them back into the darkstar database.

Code:

mysql darkstar -e "select requirements from pc_t where game_id=1;" | awk 'NR==2{print "mysql darkstar -e \"update pc_t set requirements = "$1"|"$3"|"$6" where game_id=1;\""}' | bash

which produces(without piping to bash)

Code:

mysql darkstar -e "update pc_t set requirements = 90Mhz|32MB|200MB where game_id=1;"

However, I need to put single quotes around 90Mhz|32MB|200MB. So I tried putting single quotes and escaping them.

Code:

mysql darkstar -e "select requirements from pc_t where game_id=1;" | awk 'NR==2{print "mysql darkstar -e \"update pc_t set requirements = \'"$1"|"$3"|"$6"\' where game_id=1;\""}' | bash

but the terminal gives me the next line as if there's a quote that is not closed. How can I go about doing this?
In the end I would do something like this, where the game_id is from 1 to 58.

Code:

for i in `seq 1 58`; do mysql darkstar -e "select requirements from pc_t where game_id=$i;" | awk 'NR==2{print "mysql darkstar -e \"update pc_t set requirements = "$1"|"$3"|"$6" where game_id=$i;\""}' | bash; done

catkin · 02-25-2012, 11:08 PM

Reformatting the code for legibility ...

Original

Code:

mysql darkstar -e "select requirements from pc_t where game_id=1;" \
  | awk 'NR==2{print "mysql darkstar -e \"update pc_t set requirements = "$1"|"$3"|"$6" where game_id=1;\""}' \
  | bash

Trying for single quotes

Code:

mysql darkstar -e "select requirements from pc_t where game_id=1;" \
  | awk 'NR==2{print "mysql darkstar -e \"update pc_t set requirements = \'"$1"|"$3"|"$6"\' where game_id=1;\""}' \
  | bash

Analysis ...

In bash, anything (everything!) after the opening single quote is replicated verbatim up to the closing single quote so when the bash running the script (as opposed to the bash at the end of the pipeline) parses the awk command it

Finds an opening single quote in awk 'NR==2
Finds a closing single quote in = \'"$1
Finds an opening single quote in \""}'

It would be possible to fix the quoting but multiple parsing passes like this are notoriously difficult to write and subsequently to maintain so how about (not tested):

Code:

values=$( mysql darkstar -e "select requirements from pc_t where game_id=1;" \
  | awk 'NR==2{print $1"|"$3"|"$6}' )
mysql darkstar -e "update pc_t set requirements = '$values' where game_id=1;"

trist007 · 02-26-2012, 09:37 AM

I see now, ok great fantastic.