mysql add a column to a number of tables

Question

I want to add a column to a number of tables:

my tables are in the format of No_tmp e.g 1_tmp .. 9999_tmp

No = a number 1 - 1000 ... I want to be able to add a column to all No_tmp tables every single table

Then I want to insert a text value eg "abcdef" in some tables

is it posible, I don't wish to do it manually on each table

ie

tables 1 - 223 I want to insert "guppy" into the "group" field

tables 224 - 399 I want to insert "barb" into the "group" field

tables 400 - 475 I want to insert "goldfish" into the "group" field

I figured out what I needed was the ALTER command ..

I probably did not word my question very well.

Both answers provided are good.

What I have is a large number of csv files with different sized tables, different column sizes, different numbers of columns etc, so I'm making new tables for each group of items, and logging the details of each new table in a master, from that I can analyse the structure and find common fields.

My choice of using numbers as part of the table name just makes it more readable than choosing random characters, and is easily accomplished and automated. Once I have the data I'll then start to look at normalising / formatting it.

Thank you for the answers.

Answer 1

From this question, I understand that, you need to store same data multiple times on a database. Before starting this understand that, it is not necessary to store same data multiple times on database. IF I understood correct, a simple work around will be, have a master table group with data guppy, barb, goldfishand use its primary id on a child tables.

Example:

master table:

create table group (id int, group_name char(10), id primary key) engine = innodb;

child table:

create table child1 (id int, group_id int, ..... foreign key group_id references group(id), id primary key) engine = innodb;

But, it depends on your requirement, may be after analyzing complete requirement, this model may/may not applicable. I hope this answer will help you in some way. Thank you.