MySQL Create Tables Based On Values From A Different Table

Question

What is the proper way to automatically create new tables based on values in another table? For instance, If table A has a column named city that contains different city values then I would need to create a new table based on each different city. Then all records with the respective city needs to be inserted into it's respective table. Also, if the city contains a space in the name it needs to be replaced with a an underscore. How could the same be done in MySQL?

In MS ACCESS I could accomplish this by:

Using A Select And Replace Query Named SELREP

SELECT table_A.column1, table_A.column2, table_A.city, Replace([city]," ","_") AS table_name_column FROM table_A;

Create a Public Function MakeTableCity

Public Function MakeTableCity()
DoCmd.SetWarnings False
Dim db As Database
Set db = Application.CurrentDb
Dim distinctValues As DAO.Recordset
Set distinctValues = db.OpenRecordset("SELECT table_name_column FROM SELREP GROUP BY table_name_column", dbOpenSnapshot)
Do Until distinctValues.EOF
DoCmd.RunSQL "SELECT * INTO " & distinctValues("table_name_column") & " FROM SELREP WHERE table_name_column ='" & distinctValues("table_name_column") & "'"
distinctValues.MoveNext
Loop
DoCmd.SetWarnings True
Set distinctValues = Nothing
Set db = Nothing
End Function

Why do you need a separate table for every city? MySQL is built to handle having multiple cities' data together in a single table. — mellamokb
– mellamokb, Commented May 5, 2011 at 2:41
Indeed, having multiple identically-structured tables is a strong sign of a bad design. It is also a form of denormalization. — wallyk
– wallyk, Commented May 5, 2011 at 2:50
So users of a particular city can easily navigate to their own city data. — user739072
– user739072, Commented May 5, 2011 at 3:00
Although you can partition by city, you will have fairly unbalanced partitions most likely. (e.g. Portland, OR would most likely have less data than New York, NY) — Brian
– Brian, Commented May 5, 2011 at 3:44

ypercubeᵀᴹ · Accepted Answer · 2011-05-05 17:56:15Z

If you are planning to create a new table city with data about cities, meaning one row for every city, then go ahead and read the answer.
If , on the other hand, you are planning to make a new table for every city, with identical columns, then your plan is very bad design. Read about normalization first.

First alternative is to create a table named city with fields that you want. Example:

CREATE TABLE city
( id INT auto_increment PRIMARY KEY
, name VARCHAR(50) NOT NULL
, population INT
, state CHAR(2)
) ;

Then copy the different city names into it with:

INSERT INTO city (name)
  ( SELECT DINSTINCT city     --- change "city" into REPLACE(city, ' ', '_')
    FROM table_A              --- for the small changes you want 
  ) ;

Then, update the other fields (population, state, etc).

If no two cities have same name, the JOINs between the two tables can then be done using ON table_A.city = city.name

If not, (and better anyway as the Primary Key of city will be smaller), you may ALTER the structure of table table_A by adding a field cityid and dropping the city field. Then the JOINs between the two tables will be done using ON table_A.cityid = city.id

Second option is to directly create table city with:

CREATE TABLE city AS
  ( SELECT DINSTINCT city AS name  --- change "city" into REPLACE(city, ' ', '_')
    FROM table_A                   --- for the small changes you want 
  ) ;

and then alter the table defining Primary Key, adding (population, state, etc).

Collectives™ on Stack Overflow

MySQL Create Tables Based On Values From A Different Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related