List:General Discussion« Previous MessageNext Message »
From:joe j Date:April 4 2012 5:56pm
Subject:Re: create multiple tables in a single query
View as plain text  
Thanks to Rick I would go for a single table. I tried the following,
but hasn't been successful yet. First I created one 'master' table for
one country, with a country column (ctry) as Rick suggested, then I
tried to use the loop suggested by Johan to insert the records for
each of the remaining countries into the master table:

/*STEP 1. Create a master table with for one country */
CREATE TABLE `table_new`
SELECT var1, var2`
, (case when (country='US') then 1 else 0 end) AS citizen
,'US' AS ctry
FROM `USA_table`
LEFT JOIN
WORLD
USING(YEAR);

/*STEP 2. from the dos prompt?*/

for x in UK ZA IN CN;
do mysql  -ujoe -p -e "INSERT INTO  `table_new`
SELECT var1, var2`, (case when (country='${x}') then 1 else 0 end) AS citizen
,'${x}' AS ctry
FROM `'${x}'_table`
LEFT JOIN
WORLD
USING(YEAR)";

For some reasons step2 is not working.

I assume step 2 must be run from DOS prompt (my mysql runs on a
server, and I am using PuTTY).  Any thoughts?
Thread
create multiple tables in a single queryjoe j3 Apr
  • Re: create multiple tables in a single queryhsv4 Apr
    • Re: create multiple tables in a single queryRick James4 Apr
      • Re: create multiple tables in a single queryJohan De Meersman5 Apr
  • Re: create multiple tables in a single queryJohan De Meersman4 Apr
    • Re: create multiple tables in a single queryjoe j4 Apr
      • Re: create multiple tables in a single queryjoe j4 Apr
        • Re: create multiple tables in a single queryJohan De Meersman5 Apr
          • Re: create multiple tables in a single queryjoe j5 Apr
            • Re: create multiple tables in a single queryjoe j5 Apr
              • Re: create multiple tables in a single queryJohan De Meersman6 Apr
        • Re: create more tables in a single queryhsv5 Apr
Re: create multiple tables in a single queryJan Steinman5 Apr