CODEWITHSUNDEEP
mysqlsql
Menlo123
Menlo123

Reputation: 63

Select data from one table and insert into another existing table, which doesn't exist in the table

My table scheme is as follows: (Bold column name is primary key)

Table 1: id1 - id2

Table 2: id2 - name2

Table 3: id3 - name3

Table 4: id1 - Id3

What I want to do is have sql code that :

  1. Select data in id1 and id3 columns for which name2=input=name3
  2. Insert into table 4
  3. Only insert into 4 if id1, id3 combination doesn't exist in table 4

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

This is my code currently:

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'

Upvotes: 6

Views: 7789

Answers (3)

Tzook Bar Noy
Tzook Bar Noy

Reputation: 11677

your current query is ok for the insertion, but if you want to deny inserts if that combination already exists, simply add a primary key to table4, that contains those 2 columns.

In the query do:

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;

that is just for making the query still run, if there is a duplicate it will do nothing.

Upvotes: 0

roman
roman

Reputation: 117347

Here the query you need

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 an advice - I suggest you always use aliases (and refer to column as alias.column_name) in your queries, it'll help you to avoid bugs and your queries will be more readable.

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

I think you are looking for this

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 (outer join) gets all the records in table2 whether they exist or not. If they don't table3.name3 will be null, so those are the chaps you want.

Upvotes: 0

Related Questions