Modifying MySQL table using BASH array

Question

I have a MySQL column which got incorrectly loaded with data. I created a script which reads the text file of 14 million primary keys I know got messed up. I have a basic bash while loop but instead of issuing a separate mysql command for every id, I'd like to do it for a set of id's (I do have 14 million after all).

currently:

while IFS='' read -r id || [[ -n "$id" ]]; do
    mysql -e "UPDATE my_table SET direct = 1 WHERE id = $id" -u $USER -p$PASS db
done < "$1"

what I would like to do is read in a set of id's and say something like WHERE id in ($#ids) without having to perform a bunch of loops and modulos. Ideas?

doable, but bear in mind the normal command line and query string length limits. you'll only be able to stuff so many ids onto the cli before the shell will barf/truncate. — Marc B
– Marc B, Commented Aug 4, 2016 at 17:06
Query length limits? I was thinking about building up a $STRING variable as I read in the file so $STRING looks like "1283792, 7657887263, 16876873", etc. then dumping that into the in () clause. — NastyDiaper
– NastyDiaper, Commented Aug 4, 2016 at 17:20
max_allowed_packet in mysql. any longer than that will be... not good. — Marc B
– Marc B, Commented Aug 4, 2016 at 17:21

janos · Accepted Answer · 2016-08-04 17:52:29Z

1

If you have ids in an array, and they don't contain commas, then you can join the values by , and formulate an IN-query like this:

mysql -e "UPDATE my_table SET direct = 1 WHERE id IN ($(IFS=,; echo "${ids[*]}"))" -u $USER -p$PASS db

This solves one part of your problem.

Another part is to split the 14 million ideas to workable chunks. Something like this should work:

execute_update() {
    mysql -e "UPDATE my_table SET direct = 1 WHERE id IN $(IFS=,; echo "${ids[*]}")" -u $USER -p$PASS db
}

ids=()
while IFS='' read -r id; do
    ids+=($id)
    if [[ ${#ids[@]} = 200 ]]; then
        execute_update
        ids=()
    fi
done < "$1"

if [[ ${#ids[@]} < 200 ]]; then
    execute_update
    ids=()
fi

edited Aug 4, 2016 at 17:52

answered Aug 4, 2016 at 17:21

janos

126k31 gold badges242 silver badges253 bronze badges

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

1 Comment

janos Over a year ago

Oopsie, my bad, good you could figure it out, updated the post now in any case

Collectives™ on Stack Overflow

Modifying MySQL table using BASH array

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