0

I have two mysql tables with a one-to-many relationship.

I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one.

How can I edit my query to achieve this?

INSERT INTO many_table SET 
    id = ( SELECT id 
           FROM one_table 
           JOIN many_table ON one_table.id = many_table.related_id 
           WHERE many_table.related_id is null
         ) 
    name = 'george'
Improve this question
2
  • would something like this work INSERT INTO many_table SET id = ( SELECT id FROM one_table JOIN many_table ON one_table.id = many_table.related_id WHERE many_table.related_id is null) name = 'george' – megaman Feb 14 '20 at 0:37
  • Providing a minimal reproducible example is the best place to start for questions on query help. You can see here that there's a syntax error in what you've suggested in comments. I suggest playing with the fiddle and adding details based on that to your question. – Peter Vandivier Feb 14 '20 at 9:02
0

Not sure what is name = 'george' is doing but assume it's part of the filtering in WHERE clause.

INSERT INTO many_table (id, name)
SELECT o.id, o.name
FROM one_table o
LEFT JOIN many_table m ON m.related_id = o.id
WHERE o.name = 'george' AND m.id IS NULL
Improve this answer
3
  • but assume it's part of the filtering in WHERE clause. I think it is an expression in SET (simply comma is lost) - 'george' is a value which must be inserted into NOT NULL field as a default value which is not set or is wrongly set in table structure. – Akina Feb 14 '20 at 5:13
  • I was unclear with the name = 'george' bit what i was trying to show is code which would read a list of IDs from the one_table and for each one create a row in the many_table filling in the many_tables related_id field every time the name = george was supposed to add the value george to a name column (which i previously did not mention) to every new row created in the many table – megaman Feb 14 '20 at 12:40
  • @megaman Updated. – Eric Feb 14 '20 at 17:15
0
INSERT INTO many_table(id,name)

 SELECT  id ,'george'
           FROM one_table
           LEFT JOIN many_table ON one_table.id = many_table.related_id 
           WHERE many_table.related_id is null
Improve this answer
1
  • How does this differ from the other answer? – Michael Green Feb 22 '20 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.