Question 221088 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I insert data into one table I created by referencing a column in another table I created in SQL?

I have created two tables, animation_companies and cartoon_characters. I am given a "document to simulate a request from a supervisor to insert information into a database I have created. The data reads as follows:

The Animation_Companies table will contain only these three (3) records:

Warner Bros.
Disney
Nickelodeon

The Cartoon_Characters table will contain only fourteen (14) records containing the name of the cartoon, the year it was created, and the company that owns the cartoon.

The companies "Warner Bros.", "Disney", and "Nickelodeon" are for display purposes only. It must be referenced from the Animation_Companies table.

|Cartoon Name   |Creation Year  |Company
| ------------- | ------------- |----------- |
|Porky Pig      |1935           |Warner Bros.|
|Daffy Duck     |1937           |Warner Bros.|
|Elmer Fudd     |1937           |Warner Bros.|
|Bugs Bunny     |1940           |Warner Bros.|
|Foghorn Leghorn|1946           |Warner Bros.|
|Mickey Mouse   |1928           |Disney      |
|Goofy          |1934           |Disney      |
|Donald Duck    |1931           |Disney      |
|Elsa of Arendelle|2013         |Disney      |
|Anna of Arendelle|2013         |Disney      |
|Doug Funnie    |1991           |Nickelodeon |
|Arnold Shortman|1985           |Nickelodeon |
|Tommy Pickles  |1991           |Nickelodeon |
|Aang           |2005           |Nickelodeon |

The table animation_companies was created using this statement:

CREATE TABLE Animation_Companies 
(
 CompanyID INT PRIMARY KEY AUTO_INCREMENT, 
 CompanyName VARCHAR(100) NOT NULL 
); 

The table cartoon_characters was created using this statement:

CREATE TABLE Cartoon_Characters 
(
  cartoonID INT PRIMARY KEY AUTO_INCREMENT, 
  cartoonName VARCHAR(100) NOT NULL, 
  yearCreated INT,        
  OwningCompany INT NOT NULL, 
  CompanyID INT NOT NULL, 
    CONSTRAINT Cartoon_Characters_fk_Animation_Companies 
    FOREIGN KEY (CompanyID) REFERENCES Animation_Companies (CompanyID)
);

I have inserted data into records for animation_companies using:

INSERT INTO animation_companies(CompanyName) 
VALUES ('Warner Bros.'), ('Disney'), ('Nickelodeon');

The question I am posed with reads as follows:

"Insert data into the Cartoon_Characters table:

Without using a column list, write the statement to insert the cartoon name, the year it was created, and which company it belongs to.

Notes:

Wrap strings in single quotes
Watch your spacing
Auto incrementing values uses DEFAULT
The company is referenced by the integer ID"

This statement is what I tried:

INSERT INTO cartoon_characters
VALUES (DEFAULT, 'Porky Pig', 1935, 'Warner Bros.', 1),
       (DEFAULT, 'Daffy Duck', 1937, 'Warner Bros.', 1),
       (DEFAULT, 'Elmer Fudd', 1937, 'Warner Bros.', 1),
       (DEFAULT, 'Bugs Bunny', 1940, 'Warner Bros.', 1), 
       (DEFAULT, 'Foghorn Leghorn', 1946, 'Warner Bros.', 1),
       (DEFAULT, 'Mickey Mouse', 1928, 'Disney', 2),
       (DEFAULT, 'Goofy', 1934, 'Disney', 2),
       (DEFAULT, 'Donald Duck', 1931, 'Disney', 2),
       (DEFAULT, 'Elsa of Arendelle', 2013, 'Disney', 2),
       (DEFAULT, 'Anna of Arendelle', 2013, 'Disney', 2),
       (DEFAULT, 'Doug Funnie', 1991, 'Nickelodeon', 3),
       (DEFAULT, 'Arnold Shortman', 1985, 'Nickelodeon', 3),
       (DEFAULT, 'Tommy Pickles', 1991, 'Nickelodeon', 3),
       (DEFAULT, 'Aang' 2005, 'Nickelodeon', 3);
Tags:

2

Answers


  1. 0

    A method would be to insert the data into a temp table first.

    INSERT INTO #SomeTempTable
VALUES ('Porky Pig', 1935, 'Warner Bros.', 'Warner Bros');

    Then you JOIN it to the Animation_Companies table

    INSERT INTO cartoon_characters(cartoonName, YearCreated, OwningCompany, CompanyId)
SELECT t.cartoonName, t.YearCreated, t.OwningCompany, a.CompanyId
FROM #SomeTempTable t
JOIN Animation_Companies a ON a.CompanyName = t.CompanyName
    Login or Signup to reply.
  2. 0

    2 mistakes

    1. last insert DEFAULT, ‘Aang’ 2005, ‘Nickelodeon’, 3) is missing a
      comma after ‘Aang’
    2. owning company name is int in the cartoon_characters table but you
      are passing a string which will throw an error. This is redundant and can be discarded. You can get the name in a select using join.

    Also be consistent in your table names (and column names) just in case you run into case sensitivity issues.

    Otherwise your insert is correct.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search