Sql insert if row does not exist

问题:

I have 3 tables called table1 table2 and table3. the table3 contains records that have table1.id and table2.id and some other columns as well. So I need to do the following. for each record in table 1 I need to see if in table3 there is a row containing that table1.id and any other table2.id if there is no such record I want to insert it.

so here is the example.

suppose table1

1 ... ... ...
2 ... ... ...

table2

10 .. .. ..
20 .. .. ..

table3

1 | 10 .. .. ..
2 | 20 .. .. ..

I need to add

1 20 .. .. .. and 2 10 .. .. .. rows to the table3 because for table1.id 1 it did not have the row which had all table2.ids (in this case 20) and for table1.id 2 it also did not have the row which had all table2.ids (in this case 10) in it. any help would be appreciated

回答1:

If I've got it right try this:

INSERT INTO Table3 (Table1_id,Table2_id)
SELECT Tablei.id,Table2.id FROM Table1,Table2
  WHERE NOT EXISTS (SELECT 1 
                          FROM Table3 
                          WHERE Table3.Table1_id=Table1.ID 
                                AND
                                Table3.Table2_id=Table2.ID)

回答2:

Try this:

IF NOT EXISTS(SELECT 1 FROM Table3 WHERE Table3.Table1_ID = Table1.ID AND Table3.Table2_ID = Table2.ID)
    INSERT INTO Table3(Table1_ID, Table2_ID) VALUES (ID1,ID2)
END IF

回答3:

You can also make a cross join and them insert the combinations that do not exist from that cross join.

sqlFiddle

insert into table3(col1, col2)
  select t.a, t.b
    from table3
   right join (select table1.col as a, table2.col as b
                 from table1
                cross join table2) t on t.a = table3.col1 and t.b = table3.col2

   where table3.col1 is null
     and table3.col2 is null;

回答4:

Another syntax would be:

INSERT INTO t3 (t1id, t2id ) 
SELECT 
    t1.id
    , t2.id
FROM
    t1,t2
EXCEPT
    SELECT t1id, t2id from t3

And furthermore you could add triggers on t1 and t2 to fulfill the task automatically.