2

I have a table setup with a UNIQUE column called, for example, test. I want to insert a row with the column test only if there isn't already a row in the table with test. I know I could just do the INSERT query and it would throw up an error if it already existed (and wouldn't really cause any harm AFAIK), but is there a way to do this properly using only MySQL? I'm pretty sure it can be done with functions but I've never used those before and I think there's an easier way.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Tom Marthenal
  • 3,066
  • 3
  • 32
  • 47

4 Answers4

4

Sounds like a job for INSERT IGNORE:

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.

martin clayton
  • 76,436
  • 32
  • 213
  • 198
  • 2
    http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – m4tt1mus Dec 01 '10 at 00:57
  • 1
    @Tom - recommend that you click through on the link to Bill Karwin's caveat for INSERT IGNORE posted by mattimus. Basically, you might want to consider ON DUPLICATE KEY UPDATE - depends what exactly you're doing. – martin clayton Dec 01 '10 at 01:08
  • I looked; the table I have only have two columns, both of which in this case will be identical, but that's a great tip. Thanks for your help! – Tom Marthenal Dec 01 '10 at 01:09
2

Something like this should work

INSERT INTO TABLE(column1,column2)
SELECT value1, value2
WHERE 1 NOT IN (SELECT 1 FROM TABLE WHERE test='test')
John
  • 5,672
  • 7
  • 34
  • 52
1

You can use the IGNORE keyword, like this:

INSERT IGNORE INTO table_name (test) VALUES('my_value');

From the MySQL documentation:

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.

If you want to update the existing row rather than ignore the duplicate update entirely, check out the ON DUPLICATE KEY UPDATE syntax.

incidentist
  • 403
  • 5
  • 9
0

INSERT IGNORE (v,b,g) VALUES(1.2.3) will do nothing if you hit keys (primary or unique) but you should know your keys then. or just as John said, with preselcted data

Michael Tabolsky
  • 3,429
  • 2
  • 18
  • 11