Select data from one table and paste into another existing table that does not exist in the table

Question

My table layout is as follows: (A bold column is a primary key)

Table 1: id1 - id2

Table 2: id2 - name2

Table 3: id3 - name3

Table 4: id1 - Id3

What I want to do is to have sql code that:

• Select data in columns id1 and id3 for which name2 = input = name3
• Paste in table 4
• Insert only 4 if id1, id3 combination does not exist in table 4

Currently I can do steps 1 and 2, but (assuming it can be done) I cannot get the syntax for "NOT EXIST" for step 3.

This is my code:

INSERT INTO table4( id1, id3) SELECT id1, id3 FROM table2 INNER JOIN table1 ON table1.id2 = table2.id2 INNER JOIN table3 ON table2.name2 = table3.name3 WHERE name2 LIKE 'input' 

Answer 1

Need a request here

 insert into table4(id1, id3) select t1.id1, t3.id3 from table2 as t2 inner join table1 as t1 on t1.id2 = t2.id2 inner join table3 as t2 on t2.name2 = t3.name3 where t2.name2 like 'input' and not exists ( select * from table4 as t4 where t4.id1 = t1.id1 and t4.id3 = t3.id3 ) 

as a tip - I suggest you always use aliases (and refer to the column as alias.column_name ) in your queries, this will help you avoid errors, and your queries will be more readable.

Answer 2

I think you are looking for it.

 INSERT INTO table4( id1, id3) SELECT id1, id3 FROM table2 INNER JOIN table1 ON table1.id2 = table2.id2 Left JOIN table3 ON table2.name2 = table3.name3 WHERE name2 LIKE 'input' and table3.name3 is null 

or something similar. Left (external join) gets all the entries in table2 regardless of whether they exist or not. If they are not table3.name3, this will be null, so these are the chapters you need.

Answer 3

your current request is approved for insertion, but if you want to prohibit insertions, if this combination already exists, just add the primary key to table4 containing these 2 columns.

In the request, run:

 INSERT INTO table4( id1, id3) SELECT id1, id3 FROM table2 INNER JOIN table1 ON table1.id2 = table2.id2 INNER JOIN table3 ON table2.name2 = table3.name3 WHERE name2 LIKE 'input' ON DUPLICATE KEY UPDATE id1=id1; 

which is intended only to fulfill the request, if there is a duplicate, it will not do anything.