run same sql queries for different tables at a time (bash script)

Question

I would like to add same columns into my multiple tables at a time using bash script from my Linux virtual machine terminal. I have three tables in my database:
my_usa_table,
my_india_table,
my_germany_table

I want to add following columns for those three tables at a time.
ALTER TABLE my_{$i}table ADD COLUMN state VARCHAR(120) AFTER address;
ALTER TABLE my{$i}table ADD COLUMN zipcode VARCHAR(16) AFTER state;
ALTER TABLE my{$i}_table ADD COLUMN language VARCHAR(100) AFTER zipcode;

Here is my bash script:

#!/bin/bash

declare -a country=("usa","india","germany")
for i in "${country[@]}"
do
    mysql -uroot -p testdb -e "ALTER TABLE my_{$i}_table ADD COLUMN state VARCHAR(120) AFTER address";
    mysql -uroot -p testdb -e "ALTER TABLE my_{$i}_table ADD COLUMN zipcode VARCHAR(16) AFTER state";
    mysql -uroot -p testdb -e "ALTER TABLE my_{$i}_table ADD COLUMN language VARCHAR(100) AFTER zipcode";

done

But it is getting error. How may i fix it please ?

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{usa}_company ADD COLUMN state VARCHAR(120) AFTER address' at line 1 — rhb1
– rhb1, Commented Dec 8, 2015 at 9:45
you tagged both mysql and sqlserver. which database you are using? — bmsqldev
– bmsqldev, Commented Dec 8, 2015 at 9:47
You say you want to do them "at a time". The ";" at the end of each mysql doesn't do anything. semi-colon is for separating commands on the same line, so that has no effect. Why not enclose the 3 mysqls into an anonymous () block, run that in the background with & after it, and have all 3 countries running at once? Assuming these tables are quite big, that'd save time. Also I don't know mysql but I'd expect it to be able to add the 3 columns concurrently, from a single statement . — Abe Crabtree
– Abe Crabtree, Commented Dec 9, 2015 at 9:45

Gouda Elalfy · Accepted Answer · 2015-12-08 09:47:34Z

1

try to replace my_{$i}_table wit this my_$i_table

answered Dec 8, 2015 at 9:47

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

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

1 Comment

Cyrus Over a year ago

or replace my_{$i}_table by my_${i}_table.

Collectives™ on Stack Overflow

run same sql queries for different tables at a time (bash script)

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