Insert Where Not Exists-Without Primary Key

Question

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

So I'm trying to make a query where it will insert rows into groupdentlink (linking all dentists in the state with all the groups in the state) but only if those rows don't already exist. In a nutshell I want to add new rows without overwriting existing ones or duplicating them.

So the intent of the query is something like:

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 don't have any primary keys in the groupdentlink table.

Thank you in advance!

Filip Roséen - refp · Accepted Answer · 2011-12-16T23:04:21.077

23

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

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 don't need primary keys to make MySQL handle this for you, you should add a UNIQUE key constraint on the combined set of the two columns.

Query to add the unique key 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 on 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 to your table, if it fails due to a key constraint it will act like nothing happen.

edited Dec 16 '11 at 23:04

answered Dec 16 '11 at 13:15

Filip Roséen - refp

62,493
20
150
196

OK, but you won't get an error if it fails for different reasons, like a foreign key constraint violation. – GeertPt Dec 16 '11 at 13:26
@greyfairer It will only "IGNORE" if the insert fails due to a duplicate key, foreign key constraints still apply. – Filip Roséen - refp Dec 16 '11 at 13:37
Thank you very much. I'm using the second method: Unique key from combined columns since it seems simpler to me. – Brian Barrus Dec 16 '11 at 22:50
You don't need the `LIMIT 1` in the subquery, MySQL automatically stops after a single row is found in a EXISTS clause – Hans Z Aug 31 '16 at 18:30

GeertPt · Answer 2 · 2011-12-16T13:22:58.240

5

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 on the combination (f_dent_id, f_group_id) just to make sure.

edited Dec 16 '11 at 13:22

answered Dec 15 '11 at 19:17

GeertPt

16,398
2
37
61

As a tip, use `1` instead of `*` on your `exists` statements so that you're not trying to pull back a large column set in memory. Speeds up the query a bit (can have a big impact the larger the query gets, and depends on the engine). – Eric Dec 15 '11 at 19:20
@Eric, that is not the case. See the [Docs](http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html): Traditionally, an EXISTS subquery starts with SELECT * , but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference. – The Scrum Meister Dec 15 '11 at 19:26
Ah, MySQL doesn't do that--older versions of SQL Server used to (don't know if they still do), and that's where all my work is done in. I use it as a best practice. – Eric Dec 15 '11 at 19:30
Can't find any errant parentheses, commas, quote marks yet. Any ideas? The code you posted looks good? Mysql server version is 5.1.60. – Brian Barrus Dec 15 '11 at 20:00

score 2 · Answer 3 · answered Dec 15 '11 at 19:17

2

You've almost got it! You can use a select statement to feed an insert statement. Just do this:

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'
    )

answered Dec 15 '11 at 19:17

Eric

92,005
12
114
115

hmm. Here's my query: "INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) SELECT '$_POST[id]', '$rowv[id]', '$row[f_sched_id]' WHERE NOT EXISTS ( SELECT 1 FROM groupdentlink WHERE f_dent_id = '$_POST[id]' AND f_group_id = '$groupid'" ...and I'm getting a syntax error – Brian Barrus Dec 15 '11 at 19:40
You need the closing paren on the exists clause. – Eric Dec 15 '11 at 19:48
What's the syntax error that you're getting, or is it not specific? – Eric Dec 15 '11 at 20:09
Huh...can you just do the select statement that's within the `not exists` clause? – Eric Dec 15 '11 at 20:15
Here's my full query: "INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id) VALUES ('$_POST[memberid]', '$rowv[id]', '$rowv[f_sched_id]') WHERE NOT EXISTS ( select * FROM groupdentlink where f_dent_id = '$_POST[memberid]' and f_group_id = '$rowv[id]' )" – Brian Barrus Dec 15 '11 at 20:20
Sorry, I had changed some of the values earlier to make it easier to explain but this is the actual query. Any ideas? – Brian Barrus Dec 15 '11 at 20:25

score 1 · Answer 4 · edited Dec 16 '11 at 13:28

1

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 on the combination (f_dent_id, f_group_id).

edited Dec 16 '11 at 13:28

Brian Mains

50,520
35
148
257

answered Dec 16 '11 at 13:13

Mahesh Badiger

19
1

Insert Where Not Exists-Without Primary Key

4 Answers4

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

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

Linked