6

The MySQL manual states:

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

What if you have a table that does not have a UNIQUE index or PRIMARY KEY, but you don't want to duplicate an entry for a column (say user_id).

Is there a way to do it in MySQL?

B Seven
  • 39,557
  • 59
  • 208
  • 346
  • 3
    Is there a reason why you wouldn't set a unique, identifying column to be a UNIQUE index? – Dancrumb Jun 22 '12 at 15:49
  • Your best option is to add a `UNIQUE` index for that column. – hafichuk Jun 22 '12 at 15:53
  • 2
    If you have tables with no primary key, then that must be fixed before you do anything else. – HLGEM Jun 22 '12 at 17:22
  • I am not designing the database. I wasn't quite sure how it would be used in the future, when other columns are added to the table. But it seems that the best thing to do is add UNIQUE. If the column isn't unique, it probably wouldn't make much sense anyway. – B Seven Jun 22 '12 at 22:13

3 Answers3

6
INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
                  WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1

Source: How to 'insert if not exists' in MySQL?

Community
  • 1
  • 1
Kermit
  • 32,563
  • 10
  • 80
  • 117
  • 4
    my case is same but what i want is if record exists then update that record, is it possible without unique & primary key – pitu Jun 04 '15 at 04:02
2

Try this:

INSERT INTO `table` (userid)
SELECT 'value for userid' FROM `table` 
  WHERE userid = 'value for userid' HAVING count(*) = 0
nawfal
  • 62,042
  • 48
  • 302
  • 339
Jeetendra Pujari
  • 1,184
  • 1
  • 14
  • 27
1

If you want the database to control data integrity, then a UNIQUE index is simply MYSQL's way of creating a UNIQUE constraint on that data.

If you don't want the database to be in charge of data integrity, then you will need to control the data some other way, through your application or ETL code. Typically this will involve scanning the table beforehand in order to validate that the value does not already exist, and then performing an insert.

With a clearer question as to what you are trying to do, we can help give better answers.

N West
  • 6,494
  • 22
  • 39