INSERT ON DUPLICATE KEY UPDATE with last_insert_id()
mysql, stored-functions
Solution
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
Problem
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..