3

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..

ianace
  • 1,646
  • 2
  • 17
  • 31
  • Please provide SHOW CREATE TABLE. Which of the column1, column2, column3 is unique? – Devart Nov 29 '12 at 10:57
  • possible duplicate of [MySQL ON DUPLICATE KEY - last insert id?](http://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id) – Steve Chambers Jul 07 '14 at 15:26

2 Answers2

6

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
KaeL
  • 3,639
  • 2
  • 28
  • 56
  • 2
    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. – Robin Castlin Nov 29 '12 at 10:53
  • I always had that weird incrementing of `Id`, sometimes they had gaps. Thanks for pointing that out @RobinCastlin – KaeL Nov 29 '12 at 10:57
  • please check the addendum :D – ianace Nov 29 '12 at 11:17
  • 1
    @KaeL 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 – ianace Nov 29 '12 at 11:18
  • @ianace: Have you tried the query above? According to the link, it works on tables that contain `AUTO_INCREMENT` column for `id`. – KaeL Nov 29 '12 at 11:19
  • @KaeL i placed it in my addendum: i cant change the index because it is being referenced in another table – ianace Nov 29 '12 at 11:25
  • @RobinCastlin what are these 'othere' reliable ways of doing it? – ianace Nov 29 '12 at 11:26
  • 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. – Robin Castlin Nov 29 '12 at 14:45
0

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.

Community
  • 1
  • 1
Michael Ryan Soileau
  • 1,763
  • 17
  • 28
  • this was pointed out on the first comment on the accepted answer – ianace Dec 09 '16 at 00:44
  • 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. – Michael Ryan Soileau Dec 09 '16 at 14:36