Unsure how to write an INSERT INTO using two tables and incorporate an IF NOT EXISTS

user_14873261 0
Viewed 37

I am using SQLITE and I have two tables Teams and Fixtures.

I want to add new fixtures using the ID from the Teams table as the Team_Name but if the Team_Name doesn’t exist in Teams I want to add it to Teams before then doing the INSERT INTO Fixture.

I have this so far ...

INSERT INTO Fixture 
VALUES (
Select ID from Teams where Team_Name = ‘Home Team’
);

Which I think will work where the name exists. (I’m unable to test as away from the computer and trying to write the codes in notes to test later).

I have tried looking on multiple sites and I have a basic SQL for beginners book but I’m not sure how to write in the extra bits as all I can find is regarding INSERT INTO IF NOT EXISTS using one table.

I hope I’ve written this question suitably but any advice and guidance on better putting questions across would be gratefully received as I am new to SQL and these forums.

sql-insert parent-child sqlite
1 Answers

Use INSERT OR IGNORE first to try to insert both team names in Teams and then insert the new row in Fixtures.
If a team's name already exist in the table nothing will happen.
This will work if there is a UNIQUE constraint for Team_Name, which makes sense:

INSERT OR IGNORE INTO Teams(Team_Name) VALUES 
  ('A Name For Home Team'),
  ('A Name For Away Team');

INSERT INTO Fixtures (HomeTeamId, AwayTeamId) VALUES
(
  (SELECT ID FROM Teams WHERE Team_Name = 'A Name For Home Team'),
  (SELECT ID FROM Teams WHERE Team_Name = 'A Name For Away Team')
);
user_104988280
Related