CODEWITHSUNDEEP
phpmysqldatabase
user739072
user739072

Reputation: 33

MySQL Create Tables Based On Values From A Different Table

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:

  1. 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;

  2. 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

Upvotes: 3

Views: 3609

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

  • 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).

Upvotes: 1

Related Questions