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

Mysql – How do I fill in the values of a foreign key when extracting data from a flat file into a relational database in SQL

I have 3 tables

1 table with all the information which I want to extract to two tables

[flat file]

name / team / town

[team table]

teamID/ team / town

[person table]

personID/ name / teamID

now I can use INSERT INTO SELECT to fill the values of the team table but i have hit a roadblock because I cant find a way to put the corresponding teamID in with the team that they are in.

I tried something like this but it just ends up repeating the name with each and every teamID

INSERT INTO person(name,teamID)

SELECT DISTINCT name,teamID FROM file,team
Tags:

2

Answers


  1. 0

    When you use Joins you will get the wanted results for your table

    INSERT INTO person(name,teamID)    
SELECT DISTINCT f.name,t.teamID FROM file f  JOIN team t ON f.team = t.team
    Login or Signup to reply.
  2. 0

    You can use the query below –

    INSERT INTO person(name,teamID)
    SELECT DISTINCT f.name,t.teamID
    FROM file as f
    JOIN team as t
    ON f.teamID = t.teamID;

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

Back To Top
Search