Sql insert if row does not exist

Question

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

valex · Accepted Answer · 2014-01-14 10:04:13Z

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)

Ajay · Accepted Answer · 2014-01-14 10:10:56Z

9

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

answered Jan 14, 2014 at 10:10

Ajay

6,59818 gold badges83 silver badges135 bronze badges

4 Comments

Jamie Marshall Over a year ago

Careful - this can result in a race condition

Raahim Fareed Over a year ago

@JamieMarshall Race Condition?

Jamie Marshall Over a year ago

@RaahimFareed - if statements are notorious for their locking behavior. They're not evaluated the same as a single statement such as the accepted answer and the state of the rows being evaluated in the if statement can change before the insert actually happens. I deal with this all the time. When you use if to check records you should almost always use an explicit transaction.

Raahim Fareed Over a year ago

@JamieMarshall Makes sense, also googled on this more, thanks.

Jose Rui Santos · Accepted Answer · 2014-01-14 10:19:07Z

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;

Nico · Accepted Answer · 2014-01-14 10:51:43Z

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.

Collectives™ on Stack Overflow

Sql insert if row does not exist

4 Answers 4

Comments

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Linked

Related