Paste where does not exist - without primary key

Question

I have 3 tables: dentists, groups and groupdentlink. Many dentists refer to many groups through the groupdentlink table.

So, I'm trying to make a request in which it will insert rows in groupdentlink (linking all dentists in state to all groups in state), but only if these rows do not exist yet. In a nutshell, I want to add new lines without overwriting existing ones or duplicating them.

So the goal of the request is something like this:

INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) VALUES ('$_POST[id]', '$groupid', '$scheduleid') WHERE NOT EXISTS ('$_POST[id]', '$groupid') 

And I do not have primary keys in the groupdentlink table.

Thank you in advance!

Answer 1

If you really want to write your own (working) request ..

---

 INSERT INTO groupdentlink ( f_dent_id, f_group_id, f_schedule_id ) SELECT '$_POST[id]' f_dent_id, '$groupid' f_group_id, '$scheduleid' f_schedule_id FROM DUAL WHERE NOT EXISTS ( SELECT 1 FROM `groupdentlink` WHERE f_dent_id = '$_POST[id]' AND f_group_id = '$groupid' LIMIT 1 -- will stop mysql to stop searching after first match ) 

... but MySQL can handle all this for you!

---

You do not need primary keys to force MySQL to handle this for you; you must add a UNIQUE key UNIQUE for the combined set of two columns.

A request to add a unique dent_group_uniq_key to groupdentlink .

 ALTER TABLE groupdentlink ADD UNIQUE KEY `dent_group_uniq_key` ( f_dent_id, f_group_id ); 

Then use INSERT IGNORE for your query:

 INSERT IGNORE INTO groupdentlink ( f_dent_id, f_group_id, f_schedule_id ) VALUES ( '$_POST[id]', '$groupid', '$scheduleid' ) 

INSERT IGNORE will try to insert a row into your table; if it fails due to a key constraint, it will act as if nothing was happening.

Answer 2

 INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) SELECT '$_POST[id]', '$groupid', '$scheduleid' FROM dual WHERE NOT EXISTS ( select * from groupdentlink where f_dent_id='$_POST[id]' and f_group_id='$groupid' ) 

And I think you can create a composite primary key in combination (f_dent_id, f_group_id) to make sure.

Answer 3

You almost got it! You can use the select statement to feed the insert statement. Just do the following:

 INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) SELECT '$_POST[id]', '$groupid', '$scheduleid' WHERE NOT EXISTS ( select 1 from groupdentlink where f_dent_id = $_POST[id] and f_group_id = '$groupid' ) 

Answer 4

 INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) VALUES ('$_POST[id]', '$groupid', '$scheduleid') WHERE NOT EXISTS ( select * from groupdentlink where f_dent_id='$_POST[id]' and f_group_id='$groupid' ) 

And I think you can create a composite primary key in combination (f_dent_id, f_group_id).