0

I'm not optimistic that this can be done without a stored procedure, but I'm curious if the following is possible.

I want to write a single query insert/update that updates a row if it finds a match and if not inserts into the table with the values it would have been updating.

So... something like

updateInsert into  table_a set n = 'foo' where p='bar';

in the event that there is no row where p='bar' it would automatically insert into table_a set n = 'foo';

EDIT: Based on a couple of comments I see that I need to clarify that n is not a PRIMARY KEY and the table actually needs the freedom to have duplicate rows. I just have a situation where a specific entry needs to be unique... perhaps I'm just mixing metaphors in a bad way and should pull this out into a separate table where this key is unique.

Yevgeny Simkin
  • 26,055
  • 37
  • 127
  • 228
  • 2
    Reference thread - http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – kv-prajapati May 01 '12 at 03:29
  • Does this help at all?: http://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par – dweiss May 01 '12 at 03:30

1 Answers1

0

I would enforce this with the table schema - utilize a unique multi-column key on the target table and use INSERT IGNORE INTO - it should throw an error on a duplicate key, but the insert will ignore on error.

philwinkle
  • 6,873
  • 2
  • 24
  • 44
  • right... I considered this, but my problem is that the columns that I'm checking against (in my example n) aren't PRIMARY KEYS... I get the sense that they need to be. The issue is that the table actually needs to be able to accept duplicate rows, I just want to avoid it in certain circumstances and am finding myself having to perform this two step (if exists update if not insert) procedure. – Yevgeny Simkin May 01 '12 at 03:34
  • You don't have to use primary keys. You can use a multi-column unique key. For instance, in mysql, `ALTER TABLE `mydb`.`test` ADD UNIQUE `my_multicolumn_key` ( `uid` , `fid` )` – philwinkle May 01 '12 at 03:38
  • I see. Thanks. It's not exactly what I'm looking for because as I said, I need to have the ability to have fully duplicate rows in this table (other than their row id). But this is a terrific approach for the case where the columns can be combined into unique sets. – Yevgeny Simkin May 01 '12 at 03:50