INSERT ON DUPLICATE KEY UPDATE with last_insert_id()

Question

im trying to create a function

CREATE FUNCTION `func`(param1 INT, param2 INT, param3 TEXT) RETURNS int(11)
BEGIN
INSERT INTO `table1` (`column1`, `column2`, `column3` ) 
VALUES (param1, param2, param3)
ON DUPLICATE KEY 
UPDATE `time_stamp` = UNIX_TIMESTAMP();
RETURN last_insert_id();
END

this would insert into a table a row if it doesn't exist but otherwise update it. Notice that i returned last_insert_id() which would be correct if the function would insert otherwise would be unpredictable if it updates.

I know the alternative to solving this is using separate SELECTS and identify if it exists; if it exists retrieve the id and update using that id; otherwise just do a plain INSERT.

Now my question: Is there any alternative to doing 2 sql statements as opposed to what i'm doing now?

EDIT 1

Addendum:

there is an auto incremented index. All of the values to be inserted are unique

I'd rather not alter the index since it is being referred in another table..

Standardize

Please provide SHOW CREATE TABLE. Which of the column1, column2, column3 is unique? – Acidhead

possible duplicate of MySQL ON DUPLICATE KEY - last insert id? – Liris

Answer 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;

Found it on this link. I've never tried it though, but it might help you.

EDIT 1

You might want to check out REPLACE:

REPLACE INTO table1 (column1, column2, column3) VALUES (param1, param2, param3);

This should work for tables with correct PRIMARY KEY/UNIQUE INDEX.

In the end, you'll just have to stick with:

IF (VALUES EXISTS ON TABLE ...)
    UPDATE ...
    SELECT Id;
ELSE
    INSERT ...
    RETURN last_insert_id();
END IF

Ejectment

It works terrific. I use this method all the time. Only thing worth considering is that your id's increment may act strange when relying on UNIQUE columns that isn't the increment one. However, when you get to that point, there are other reliable ways to solve it. – Mabe

I always had that weird incrementing of Id, sometimes they had gaps. Thanks for pointing that out @RobinCastlin – Ejectment

please check the addendum :D – Standardize

@Ejectment i have searched long enough to conclude that there is no 1 liner sql statement solution for that solution; instead a separate select insert or update is easier to come up with – Standardize

@ianace: Have you tried the query above? According to the link, it works on tables that contain AUTO_INCREMENT column for id. – Ejectment

@Ejectment i placed it in my addendum: i cant change the index because it is being referenced in another table – Standardize

@RobinCastlin what are these 'othere' reliable ways of doing it? – Standardize

Well, to be fully honest, I've created a function which handles all of it perfectly. However for some reason I don't feel like sharing that function with the community. It's still using INSERT and ON UPDATE though, just more advanced. – Mabe

Answer 2

Just in case anyone shows up here from Google, I ran into a problem where ON DUPLICATE KEY UPDATE kept triggering the same wrong value.

When inserting a user with only a first name and last name, it didn't AUTO_INCREMENT the primary key. The reason is we have a users table with a unique constraint on the username, but it has a default value of ''. So when you insert a user without a username, it triggers it to update the duplicate value of that username, and that random account kept getting returned as the correct one.

The solution is to make sure that only NULL is the default value for a unique key in a table that also has a separate auto-increment primary key, or that you do generate a unique value for the unique constraint.

Electricity

this was pointed out on the first comment on the accepted answer – Standardize

No, it wasn't. The issue I ran into is the default value being set to a string instead of to null. Which triggered an update for the primary key even though it shouldn't. The first comment is: Only thing worth considering is that your id's increment may act strange when relying on UNIQUE columns that isn't the increment one. However, when you get to that point, there are other reliable ways to solve it. That's definitely not an explanation of how this can cause a problem. – Electricity