MySQL Where Not Exists Insert Record based on 3 values

Question

I have a need to do an insert to MySQL where a record does not exist in the destination table already

my query

INSERT INTO comment (`mid`, `pid`, `comment_text`, `comment_date`, `comment_type`) 
 VALUES (180, 2, NULL, '2012-07-26 10:19:00', 'tag')  WHERE NOT EXISTS ( SELECT * FROM `comment` WHERE `mid`=180 AND `pid`=2 AND `comment_type`='tag')

however when this runs I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT * FROM comment WHERE mid=180 AND `pid' at line 2

Any ideas essentially i want to stop duplicate rows being added to this table when they match the values

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

try:

INSERT IGNORE INTO comment
(
 `mid`, `pid`, `comment_text`, `comment_date`, `comment_type`
)
(
    SELECT 180, 2, NULL, '2012-07-26 10:19:00', 'tag'
    FROM comment
    WHERE `mid`=180 AND
          `pid`=2 AND
          `comment_type`='tag'
    LIMIT 1
);

EDIT: Better way to do this:

to remove duplicates from a table see here

ALTER IGNORE TABLE comment ADD UNIQUE KEY ix1(mid, pid, comment_type);

INSERT IGNORE INTO comment
(
 `mid`, `pid`, `comment_text`, `comment_date`, `comment_type`
)
VALUES 
(
 SELECT 180, 2, NULL, '2012-07-26 10:19:00', 'tag'
);

edited May 23 '17 at 12:33

Community

1
1

answered Jul 26 '12 at 09:55

Omesh

27,801
6
42
51

Thanks but this is still adding a row – Justin Erswell Jul 26 '12 at 10:01

juergen d · Answer 2 · 2012-07-26T10:12:42.317

0

add an ignore

INSERT IGNORE INTO comment ...

to prevent duplicate inserts. You can't use a where clause in an insert.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

Taken from the MySQL INSERT Documentation

That means you should add a unique key to your table (if you haven't already) to make the DB check for duplicates by itself. If the DB finds a duplicate no INSERT will be made.

if you want to make an UPDATE in case of duplicates that is possible too. See here

edited Jul 26 '12 at 10:12

answered Jul 26 '12 at 09:52

juergen d

201,996
37
293
362

thanks for your reply could you expand on your answer a bit please thanks – Justin Erswell Jul 26 '12 at 10:07
thanks for that I do have a unique id which is not the field I am trying to prevent duplicates against, essentially there are 3 type of comments in this table 'text', 'tag', 'view' the user can add as many text comments as they want but only 1 tag and 1 view there are multiple users who can insert based on mid – Justin Erswell Jul 26 '12 at 10:17
To implement rather complicated conditions you could write a `before insert` trigger that checks that for you. [cancel-insert-example](http://stackoverflow.com/questions/229765/) – juergen d Jul 26 '12 at 10:23

score 0 · Answer 3 · answered Jul 26 '12 at 09:57

0

Why not just to make unique index that consists of mid, pid and comment_type columns?

answered Jul 26 '12 at 09:57

Vladimir Kadalashvili

747
2
5
17

Good suggestion but in this use case it won't work as I need other users to add a tag and also allow them to add other comment types – Justin Erswell Jul 26 '12 at 10:03

MySQL Where Not Exists Insert Record based on 3 values

3 Answers3