Insert into database if record doesn't exist?

Question

I have looked for a way to try this, and so far the bet answer is using the INSERT IGNORE syntax, but my problem is that I won't be working with the id's.

Is there a way to insert a record into the database only if it doesn't exist, if you don't know the record id?

eg: I want to check if the data in col1 ('Hello') doesn't already exist.

INSERT IGNORE INTO table_name (col1, col2) values ('Hello', 1);

Existing record:

+--------------------+
| table_name         |
+--------------------+
| id  | col1  | col2 |
+--------------------+
| 2   | Hello | 1    |
|     |       |      |

you will `INSERT` against what `id`??..your current query will update all rows!! — NoobEditor, Aug 28 '14 at 04:51
@NoobEditor I know. I'm asking how can I do this *without* that happening? — Ben, Aug 28 '14 at 04:55
@Be0wulf yes that question was what I was looking for. Voted this as dupe. — Ben, Aug 28 '14 at 05:11

Manu Singh · Answer 1 · 2014-08-28T05:18:24.600

0

Your question is not clear. MYSQL works on id only. If you want it to work on col1 then you can write something like this

INSERT IGNORE INTO table_name (col1, col2) 
values ('Hello', 1) WHERE NOT EXISTS ( SELECT col1 from table_name 
                                       WHERE col1 = 'Hello')

But again add some more description to answer it properly.

edited Aug 28 '14 at 05:18

answered Aug 28 '14 at 04:57

Manu Singh

414
4
16

What is _"Name"_ here? Is this column name?? – Manwal Aug 28 '14 at 05:14
OOPS "Name" is column name :) – Manu Singh Aug 28 '14 at 05:19
I get a syntax error "unexpected where, end of query or ';' expected" – Ben Aug 28 '14 at 05:22

Manwal · Accepted Answer · 2014-08-28T05:37:43.830

0

I believe ON DUPLICATE KEY UPDATE suits your situation

Example query:

INSERT INTO table_name (id, col1, col2) VALUES (1,'Hello', 1)
ON DUPLICATE KEY UPDATE col1 = col1,col2 = col2;

Update: I have added id column value in query. This query check if id=1 already exists then it will not insert, if not exists it will insert

Update

If you want to check duplicate for col1 column you have to add unique constrain on this column

ALTER TABLE table_name ADD UNIQUE (col1)

SO now query of inserting and checking duplicate is:

INSERT INTO table_name (col1, col2) VALUES ('Hello', 1)
ON DUPLICATE KEY UPDATE col1 = col1,col2 = col2;

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY

edited Aug 28 '14 at 05:37

answered Aug 28 '14 at 05:12

Manwal

23,450
12
63
93

This just added a new row. I think it's because there is no reference to the `id` (primary key)? – Ben Aug 28 '14 at 05:20
You don't have to give reference `id` column its automatically check duplicate in a unique index or primary key – Manwal Aug 28 '14 at 05:24
Well, like I said before, it just added a new row, with the exact same data... – Ben Aug 28 '14 at 05:26
for this example use `col1` to check for duplicate data. I'll update the question. – Ben Aug 28 '14 at 05:33
@Ben don't forgot to delete your unwanted comments after i read them – Manwal Aug 28 '14 at 05:55
The update did it :) Thanks! – Ben Aug 28 '14 at 05:57

score 0 · Answer 3 · answered Aug 28 '14 at 05:16

0

You can use ON DUPLICATE to insert a record, if existed, it will update. And if not exist, it will insert eg:

  Insert into table_name (col1, col2) values ('Hello', 1);
      ON DUPLICATE KEY UPDATE col1=values(col1),col2=values(col2)

answered Aug 28 '14 at 05:16

dieuvn3b

5,826
3
17
29

Insert into database if record doesn't exist?

3 Answers3

Update