CODEWITHSUNDEEP
phpmysql
user1427661
user1427661

Reputation: 11774

Avoid Duplicates of Unique Key Within INSERT Query

I have a MySQL query that looks like this:

INSERT INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

The only problem is that name is a unique value, which means if I ever run into duplicates, I get an error like this: 

Error storing beer data: Duplicate entry 'Hocus Pocus' for key 2

Is there a way to ensure that the SQL query does not attempt to add a unique value that already exists without running a SELECT query for the entire database?

Upvotes: 6

Views: 4324

Answers (5)

luksch
luksch

Reputation: 11712

Yes, there is. You can use the ON DUPLICATE KEY clause of mysql INSERT statement. The syntax is explained here

INSERT INTO beer(name, type, alcohol_by_volume, ...) 
  VALUES('{$name}', {$type}, '{$alcohol_by_volume}', ...)
  ON DUPLICATE KEY UPDATE 
     type={$type}, alcohol_by_volume = '{$alcohol_by_volume}', ... ;

Upvotes: 2

bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

You could of course use INSERT IGNORE INTO, like this:

INSERT IGNORE INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

You could use ON DUPLICATE KEY as well, but if you just don't want to add a row INSERT IGNORE INTO is a better choice. ON DUPLICATE KEY is better suited if you want to do something more specific when there are a duplicate.

If you decide to use ON DUPLICATE KEY - avoid using this clause on tables with multiple unique indexes. If you have a table with multiple unique indexes ON DUPLICATE KEY-clause could be giving unexpected results (You really don't have 100% control what's going to happen)

Example: - this row below only updates ONE row (if type is 1 and alcohol_by_volume 1 (and both columns are unique indexes))

ON DUPLICATE KEY UPDATE beer SET type=3 WHERE type=1 or alcohol_by_volume=1

To sum it up:

ON DUPLICATE KEY just does the work without warnings or errors when there are duplicates.

INSERT IGNORE INTO throws a warning when there are duplicates, but besides from that just ignore to insert the duplicate into the database.

Upvotes: 8

Wolfman Joe
Wolfman Joe

Reputation: 799

Quite simply - your code needs to figure out what it wants to do if something's trying to insert a duplicate name. As such, what you need to do first is run a select statement:

 SELECT * FROM beer WHERE name='{$name}'

And then run an 'if' statement off of that to determine if you got a result.

if results = 0, then go ahead and run your insert. Else ... whatever you want to do. Throw an error back to the user? Modify the database in a different way? Completely ignore it? How is this insert statement coming about? A mass update from a file? User input from a web page?

The way you're reaching this insert statement, and how it should affect your work flow, should determine exactly how you're handling that 'else'. But you should definitely handle it.

But just make sure that the select and insert statements are in a transaction together so that other folks coming in to do the same sort of stuff isn't an issue.

Upvotes: 0

Chris Forrence
Chris Forrence

Reputation: 10094

As it just so happens, there is a way in MySQL by using ON DUPLICATE KEY UPDATE. This is available since MySQL 4.1

INSERT INTO beer(name, type, alcohol_by_volume, description, image_url)
  VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}',
  '{$image_url}')
  ON DUPLICATE KEY UPDATE type=type;

You could also use INSERT IGNORE INTO... as an alternative, but the statement would still throw a warning (albeit, instead of an error).

Upvotes: 6

Borniet
Borniet

Reputation: 3546

Yes, by first selecting the name from the database, and if the result of the query is not null (zero records), then the name already exists, and you have to get another name.

Upvotes: 0

Related Questions