List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:April 5 2012 8:50am
Subject:Re: create multiple tables in a single query
View as plain text  
----- Original Message -----
> From: "joe j" <joe.stata@stripped>
> 
> 
> /*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.

Could you, perphaps, specify the reasons? Errormessages et al :-)

> I assume step 2 must be run from DOS prompt (my mysql runs on a
> server, and I am using PuTTY).  Any thoughts?

DOS? Ick. Heathen.

Anyway, that loop won't work on a DOS prompt - it's Unix stuff. I suppose you can achieve
similar things with Powershell, but that's going to be distinctly not *my* problem :-)

If your server is running on a Unix, you'll of course need to run it there. The backticks
will be an issue inside double quotes, though, and I don't see any need for quoting.
There's something weird about your query, though...

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)

There seem to be an extraneous backtick right after var2? Also, no need to quote the ${x}
bit - the shell will substitute that before MySQL sees it. Definitely remove all the
backticks from the expression - those mean "execute this here".


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
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