mysql merge scripts to create table

Question

I've came across an issue and I cant think of a way to solve it. I need to insert country names in several languages into a table on my mysql db. I found these links link1 (en) , link2 (de) etc but I dont know how to proceed in order to finally have a table looking like this:

CREATE TABLE `country` (
  `id` varchar(2) NOT NULL,
  `en` varchar(64) NOT NULL,
  `de` varchar(64) NOT NULL,
  ...
  ...
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Why do you need it? Why not to use country table from the link? — Devart
– Devart, Commented May 23, 2012 at 15:35
I need it in many languages for a multi--lang website I'm setting up — bikey77
– bikey77, Commented May 23, 2012 at 15:37

bikey77 · Accepted Answer · 2012-05-23 15:35:36Z

1

Well, I finally figured it out so I'm posting to maybe help others.

I created 2 tables (country_en) and (country_de) and then ran the following statement:

DROP table if exists `countries`;

CREATE TABLE `countries` (
  id  varchar(2), el varchar(100), de varchar(100)
);

INSERT INTO `countries`
SELECT country_en.id, el, de
FROM   country_en 
JOIN   country_de ON (country_en.id = country_de.id);

which creates the table countries and joins the other 2 tables on their common key id

answered May 23, 2012 at 15:35

bikey77

6,69221 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2012-05-23 16:07:49Z

I can suggest you another table design. Create languages table, and modify a little country table: add lang_id field and create foreign key - FOREIGN KEY (lang_id) REFERENCES languages (id). Then populate languages and country tables.

For example:

CREATE TABLE languages(
  id VARCHAR(2) NOT NULL,
  name VARCHAR(64) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE country(
  id VARCHAR(2) NOT NULL,
  lang_id VARCHAR(2) NOT NULL DEFAULT '',
  name VARCHAR(64) NOT NULL,
  PRIMARY KEY (id, lang_id),
  CONSTRAINT FK_country_languages_id FOREIGN KEY (lang_id)
  REFERENCES languages (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB;

-- Populate languages
INSERT INTO languages VALUES 
  ('en', 'English'),
  ('de', 'German');

-- Populate names from 'en' table
INSERT INTO country SELECT id, 'en', name FROM country_en;

-- Populate names from 'de' table
INSERT INTO country SELECT id, 'de', name FROM country_de;

...Where country_en and country_deare tables from your links.

Collectives™ on Stack Overflow

mysql merge scripts to create table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related