0

I am running a insert statement to insert data, but I want to check for any duplicate entries based on date and then do an entry.

All I want is if today a user enters product_name='x', 'x' is unique so that no one can enter product name x again today. But of course the next day they can.

I do not want to run a select before the insert to do the checking. Is there an alternative?

mschr
  • 8,531
  • 3
  • 21
  • 35
X10nD
  • 21,638
  • 45
  • 111
  • 152
  • http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql – CoolStraw Sep 26 '12 at 08:07
  • @CoolStraw That would be for row_id(INT) lets say for product_name(varchar) or date() – X10nD Sep 26 '12 at 08:11

2 Answers2

3

You can either use

1. Insert into... on duplicate update
2. insert.. ignore

This post will answer your question

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

Community
  • 1
  • 1
WatsMyName
  • 4,240
  • 5
  • 42
  • 73
  • That would be for row_id(INT) lets say for product_name(varchar) or date() – X10nD Sep 26 '12 at 08:10
  • 1
    @Jean, Please see last solution for the link I given, you better use insert ignore, this will skip the duplicate row and process the query further. – WatsMyName Sep 26 '12 at 08:13
  • @Jean well if database field is not unique, then i m afraid you have only the option to check beforehand using select statement – WatsMyName Sep 26 '12 at 08:15
2

You can use the mysql insert into... on duplicate update syntax which will basically enter in a new row if one isn't there, or if the new row would have caused a key constraint to kick in, then it can be used to update instead.

Lets say you have the following table:

MyTable
ID | Name
1  | Fluffeh
2  | Bobby
3  | Tables

And ID is set as the primary key in the database (meaning it CANNOT have two rows with the same value in it) you would normally try to insert like this:

insert into myTable 
    values (1, 'Fluffster');

But this would generate an error as there is already a row with ID of 1 in it.

By using the insert on duplicate update the query now looks like this:

insert into myTable 
    values (1, 'Fluffster') 
    on duplicate key update Name='Fluffster';

Now, rather than returning an error, it updates the row with the new name instead.

Edit: You can add a unique index across two columns with the following syntax:

ALTER TABLE myTable
ADD UNIQUE INDEX (ID, `name`);

This will now let you use the syntax above to insert rows while having the same ID as other rows, but only if the name is different - or in your case, add the constraint on the varchar and date fields.

Lastly, please do add this sort of information into your question to start with, would have saved everyone a bit of time :)

Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • That would be for row_id(INT) lets say for product_name(varchar) or date() – X10nD Sep 26 '12 at 08:10
  • @Jean This will work for any type of row in the database as long as it is database constrained - meaning that a varchar field would have to be set to `unique` for example. – Fluffeh Sep 26 '12 at 08:12
  • I dont want to do a unique on db. I want to control the db via script. – X10nD Sep 26 '12 at 08:13
  • @Jean Then I don't understand, if you aren't checking for a row that exists with your select, what *are* you doing with the select that you want to do in one query? – Fluffeh Sep 26 '12 at 08:14
  • @Jean well if database field is not unique, then i m afraid you have only the option to check beforehand using select statement, if exist insert it if not do nothing. Again for varchar you have lots of things to do like `"My name"` and `"my Name"`, `" my name "`, are considered to be same or different?? – WatsMyName Sep 26 '12 at 08:16
  • @LoVeSmItH I want to avoid the select statement and control the db via script. – X10nD Sep 26 '12 at 08:16
  • @Jean Okay, please explain exactly what *control the db via script* means? – Fluffeh Sep 26 '12 at 08:17
  • @Jean, its more like you want a passport size photo and you want your legs to come in the photo. If your database field is not set to unique, you can't use any mysql in built methods. You don't have any other options rather doing select beforehand – WatsMyName Sep 26 '12 at 08:19
  • @Fluffeh For today, there is an insert for product_name='x' I do not want a product_name again for 'x' today again. I can very well do this via a select statement before the insert, which I want to avoid. Is this possible without the unique for product_name. – X10nD Sep 26 '12 at 08:21
  • @Jean, do you mean that if i enter `product_name='x'` today, I can't enter same name today, but of course the next day I can enter product with same name?? – WatsMyName Sep 26 '12 at 08:25
  • 1
    @Jean See the edit. Add a unique composite key to the table (I showed you the syntax) and the statements will work as expected. – Fluffeh Sep 26 '12 at 08:30
  • @Jean If thats the case I guess this solution will not work, YOu have to use select statement beforehand. – WatsMyName Sep 26 '12 at 08:38