0
email_address
-------------
id
prefix
fqdn

Prefix and fqdn together form a unique key.

I would like to write a query which would do an insert, if the email address is not yet in the table. If the address is already in the table, it should return the id.

Is there a way of writing a query which behaves like INSERT INTO email_address(prefix, fqdn) VALUES (?, ?) ON DUPLICATE SELECT id as LAST_INSERT_ID WHERE prefix = ? AND fqdn = ? would if it were legal?

Basically I want to do an INSERT or SELECT in one query.

fadedbee
  • 42,671
  • 44
  • 178
  • 308
  • 1
    No, this is not possible in MySQL, to my knowledge. However, you could do so in your application code (PHP, etc.) without too much trouble. – Tim Burch Jan 17 '14 at 14:25
  • 1
    If `prefix` and `fqdn` form 1 unique (primary) key, why do you think you need `id` column ? Removing `id` resolves your whole non-sense workarround and tryhard selecting something you already have. – Daniel W. Jan 17 '14 at 14:26
  • 1
    What is "SELECT __or__ INSERT"? What sense that can have? – Alma Do Jan 17 '14 at 14:26
  • 1
    I agree with @TimBurch - I'd probably handle this by _default_ in the application layer; since `id` is usually best incremented by the DB, you have to `SELECT` after insertion to get it anyways... Attempt the insert, then on duplicate-key failure (or no-rows-affected, if the insert is written that way) don't report the error (probably), just perform the selection the row, as if nothing happened. Multiple questions here for this kind of design, though. – Clockwork-Muse Jan 17 '14 at 14:28
  • @DanFromGermany - Having a surrogate key allows things like changing email addresses associated with accounts (or much easier, anyways). StackExchange probably uses this in the backend, given I've changed my email address associated with this account... – Clockwork-Muse Jan 17 '14 at 14:31
  • @Clockwork-Muse this of course is true. – Daniel W. Jan 17 '14 at 14:32
  • possible duplicate of [MySQL ON DUPLICATE KEY - last insert id?](http://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id) – Daniel W. Jan 17 '14 at 14:36
  • @Clockwork-Muse Thanks, can you paste that into an answer and I'll accept it. – fadedbee Jan 17 '14 at 14:44
  • @AlmaDo It's either "`INSERT` the row and get the `LAST_INSERT_ID`" or "`SELECT` the id of the matching row". – fadedbee Jan 20 '14 at 08:39

1 Answers1

1

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;

From http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Daniel W.
  • 31,164
  • 13
  • 93
  • 151
  • You still have to perform a `SELECT` after this (or call the C API function), of course, it just means you can use `LAST_INSERT_ID()` for it; given that he already has a unique key, there isn't much need. It should be pointed out that, thankfully, the value of the supplied parameter for the function is connection-based, so is threadsafe. – Clockwork-Muse Jan 17 '14 at 15:02