Zen4All Zen4All - 5 months ago 8
MySQL Question

mysql copy database rows from database 1 multiple times to new database 2, looping through a value of database 3

I want to copy values from on table to another using mysql, while looping through a third table to set a specific value in the second.

Table 1 is called countries, with structure and data:


countries
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ countries_id + countries_name + countries_iso_code_2 + countries_iso_code_3 +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1 + Belgium + BE + BEL +
+ 2 + Netherlands + NL + NLD +
+ 3 + Germany + DE + DEU +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


The rows countries_id and countries_name need to be copied to table countries_name. For each language_id from table languages.
Table 2


countries_name
+++++++++++++++++++++++++++++++++++++++++++++++
+ countries_id + language_id + countries_name +
+++++++++++++++++++++++++++++++++++++++++++++++
+ 1 + 1 + Belgium +
+ 2 + 1 + Netherlands +
+ 3 + 1 + Germany +
+ 1 + 3 + Belgium +
+ 2 + 3 + Netherlands +
+ 3 + 3 + Germany +
+ 1 + 4 + Belgium +
+ 2 + 4 + Netherlands +
+ 3 + 4 + Germany +
+++++++++++++++++++++++++++++++++++++++++++++++


table 3


languages
+++++++++++++++++++++++++++++++++
+ languages_id + name + code +
+++++++++++++++++++++++++++++++++
+ 1 + English + en +
+ 3 + Dutch + nl +
+ 4 + German + de +
+++++++++++++++++++++++++++++++++


I know how to do this for a single pass, but not for multiple.

CREATE TABLE countries_name (


countries_id int(11) NOT NULL,


language_id int(11) NOT NULL DEFAULT 1,


countries_name varchar(64) NOT NULL,


UNIQUE countries (countries_id, language_id),


KEY idx_countries_name_zen (countries_name)


) ENGINE=MyISAM;


INSERT INTO countries_name (countries_id, countries_name)


SELECT c.countries_id, c.countries_name


FROM countries c;

Answer

http://sqlfiddle.com/#!9/bd3c7/1

If I got your goal correctly:

INSERT INTO countries_name (countries_id, language_id, countries_name)
SELECT 
c.countries_id, 
l.languages_id,
c.countries_name
FROM countries c
LEFT JOIN languages l
ON 1;
Comments