user3299633 user3299633 - 2 months ago 6
MySQL Question

Need to forumulate UPDATE statement based on corresponding value

I have a file (cardinality order) that looks like this:

ID
Name
Job


Then I have an edited bin.log from an erroneous update statement in the following format:

1
'Joe'
'Grocer'
2
'Bill'
'Mason'


What I need to do is using the corresponding "key" file, formulate update statements so the result is as follows:

update database.table set Name='Joe', Job='Grocer' WHERE ID=1;
update database.table set Name='Bill', Job='Mason' WHERE ID=2;


These needs to work for multiple columns of data, however. The first field will always be a single primary key.

Answer

Here is a rough draft of a script that does what you want. If you like it, we can improve upon it for efficiency,style,correctness.

generate_statement.sh:

#!/usr/bin/bash
read -r -a cnames <<< "$(echo $(cat cnames))"
numcols=$((${#cnames[@]}))

counter=0; sclause=""

while read -r cvalue
do
   if (( $counter == 0 ))
   then
        keys_value=$cvalue
   elif (( $counter == $numcols-1 ))
   then
        sclause="$sclause, ${cnames[$counter]}=$cvalue"
        read -r sclause <<< "$(echo $sclause | sed 's/,//')"
        echo "update database.table set $sclause where ${cnames[0]}=$keys_value;"
        sclause=""
   else
        sclause="$sclause, ${cnames[$counter]}=$cvalue"
   fi
   (( counter = ++counter % numcols))
done <<< "$(cat cvalues)"

cnames:

ID
Name
Job
Height
Weight

cvalues:

1
'Joe'
'Grocer'
60
160
2
'Bill'
'Mason'
61
170
3
'John'
'Engineer'
65
180
4
'Jack'
'Doctor'
69
190

Output:

update database.table set Name='Joe', Job='Grocer', Height=60, Weight=160 where ID=1;
update database.table set Name='Bill', Job='Mason', Height=61, Weight=170 where ID=2;
update database.table set Name='John', Job='Engineer', Height=65, Weight=180 where ID=3;
update database.table set Name='Jack', Job='Doctor', Height=69, Weight=190 where ID=4;