3

I am used to doing this in sql server

IF (SELECT COUNT(*) FROM table WHERE column1=@value1) = 0
INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)

But I can't really get it to work in MySql. Please help :)

---------------- EDIT ------------------

There shouldn't be a lot of fuss or magic involved in this, I would assume.

If an e-mail does not exist in a table then insert several values into that table.

That's it. Preferbly a one liner, to embed in programming code.

Jesper
  • 939
  • 2
  • 14
  • 30
  • possible duplicate of [How do I update if exists, insert if not (aka upsert or merge) in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) – Mitch Wheat Jan 28 '12 at 11:04
  • Is it complete query? Or just a column ? If its a column then put your complete query. – Bajrang Jan 28 '12 at 11:05
  • regarding your edit: read my answer below :-) no magic involved – Kaii Jan 28 '12 at 11:51
  • link to http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – Kaii Jan 28 '12 at 12:03

2 Answers2

6

another way to do this is using the INSERT IGNORE statement. Assuming column1 should only hold unique values, you can add a UNIQUE KEY constraint on the field (if its not your primary key already):

ALTER TABLE table ADD UNIQUE KEY column1 (column1)

you can then write your query as such:

INSERT IGNORE INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)

or if you want to automatically update the other fields, use ON DUPLICATE KEY UPDATE:

INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3) 
ON DUPLICATE KEY UPDATE column2 = @value2,  column3 = @value3

letting the database handle it automatically using unique key has several advantages:

  • better performance and scalability through usage of indexes as opposed to full table scans
  • failsafe - you can not add two rows with same unique identifier accidentally with a wrong query
  • better readability of your queries

NOTE: a unique key may also span multiple rows. stupid example: combination of "ip" and "port" may be combined as the unique identifier "connection".

Kaii
  • 18,421
  • 3
  • 33
  • 58
  • Thanks for the suggestion, but unfortunately, the e-mail is not unique. The only unique field and key is an auto incremented ID – Jesper Jan 28 '12 at 11:50
  • but it seems in your question that you try to prevent multiple entries for the same email address ... defining it unique sounds like the correct solution? – Kaii Jan 28 '12 at 11:55
  • ha ha... granted :D The DB is a little messy, and I don't want to make it worse, I suppose. LOL – Jesper Jan 28 '12 at 11:57
  • 1
    this can only make it better, not worse :-) i would use the chance to correct things here. just my 2 cents – Kaii Jan 28 '12 at 12:00
  • OK @Kaii. I cleaned up, it wasn't so bad, and I applied the unique attribute and insert ignore. So Now everything should be great. – Jesper Jan 28 '12 at 12:20
  • Thanks a lot for your help (and patience ;)) – Jesper Jan 28 '12 at 12:21
0

Also you can do it like this:

Declare @count INT;
SET @count = (SELECT COUNT(*) FROM table WHERE column1=@value1);
IF (@count = 0)
INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)
Mohammad Saberi
  • 11,594
  • 23
  • 65
  • 123