1

I would like to use "insert on duplicate key update" in a query to either insert a new row if it does not exist or update a row if it does. What I can not seem to figure out is how to use this if I do not have the unique id (because the row has not yet been created, and this ID will be autoincremented upon insert)

insert into foodchoices (unique,notunique) values (Idonthavethis,'test')
on duplicate key update notunique = 'stuff';

Now, in this example above, where it says "Idonthavethis", I do not have any unique value for this field, because it has not yet been inserted as a row. However, I do expect that this inserts somehow, I just dont know how. I expect it to act like this:

insert into foodchoices (notunique) values ('test')

BUT, if it is a field that does already exist, I WILL have that unique value. Is there some form of wildcard or something I can use for when I do not have the unique value?

livinzlife
  • 863
  • 3
  • 17
  • 33
  • How does one determine whether the record already exists? If `notunique` is genuinely not unique, then what is the difference between inserting a new row and picking any one of the multiple versions with value `'test'`? – eggyal May 02 '12 at 09:14

3 Answers3

3

I believe the answer to this is addressed in the MySQL docs:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
jcjr
  • 1,503
  • 24
  • 40
Jegra
  • 46
  • 2
0

I think what you just might try to do is select the row with the value you have (if exists then update) otherwise insert. It's just one more sentence.

I just don't see how can you compare an existing value that you just don't have (the generated ID). Plus, if the ID is DB seeded how it'll be duplicated?

Erre Efe
  • 15,387
  • 10
  • 45
  • 77
0

May be you need to alter your table structure for adding any constraint to "notunique" column. So you can:

insert into foodchoices (notunique) values ('test') on duplicate key update columntostoreyouruniqueid = unique;

if notunique has no constaint then it mean that you will have uniqueid as set. So it has to double query.

bitoshi.n
  • 2,278
  • 1
  • 16
  • 16