List:General Discussion« Previous MessageNext Message »
From:(Hal Date:April 5 2012 5:39am
Subject:Re: create more tables in a single query
View as plain text  
>>>> 2012/04/04 19:56 +0200, joe j >>>>
/*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? 
<<<<<<<<

I yet suggest that you write an SQL procedure with a constructed string passed to PREPARE.
You need a table with all the countries, and a cursor in the procedure to scan the
table--or you could use a string and step through two characters at a time. It would be
more complicated than that bit of shell-programming, but independent of your OS.

A loop with a character string somewhat thus runs:

DECLARE i INT DEFAULT 1;

WHILE i < 8 DO
 PREPARE ... SUBSTRING('UKZAINCN', i, 2) ....
 ....
 SET i = i + 2;
END WHILE;

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